How overcome, if possible, Time Series limitation acting directly on bucket collection

Working with Data
, ,
#1

I’m exploring how to use the Time Series collection in a MongoDB replica set (on-premise community edition 6.0.6).
I have define a TS collection as below:

{
  name:"ts1",
  type:"timeseries",
  options:{
    timeseries:{
      timeField:"timestamp",
      metaField:"meta",
      granularity: "seconds",
      bucketMaxSpanSeconds: 3600
      }
    },
    info: {readonly: false}
},

Below same example data from “db.system.buckets.ts1”:

 {
            _id: ObjectId("646b3ff0c5a28d25568373ae"),
            control: {
                  version: 1,
                  min: {
                        timestamp: ISODate("2023-05-22T10:12:00.000Z"),
                        cowe: 201.939,
                        pmnp10: 14,
                        hum: 62.3,
                        pmnp50: 0,
                        pmspug10: 3,
                        pm10: 60.37,
                        calpm10: '',
                        no2ae: 397.264,
                        calco: '',
                        batt: 100,
                        temp: 24,
                        pmnp05: 166,
                        co: 0.583,
                        pmspug100: 4,
                        pmaeug10: 3,
                        no2: 50.284,
                        vafe: 262.362,
                        pmnp100: 0,
                        pmaeug25: 4,
                        no2o3ae: 409.052,
                        calpm2_5: '',
                        pmaeug100: 4,
                        no2we: 109.925,
                        pm25: 198.91,
                        no2o3we: 422.077,
                        calno2: '',
                        pmnp03: 537,
                        calo3: '',
                        pmnp25: 4,
                        pmspug25: 4,
                        coae: 160.378,
                        pmdiag: 0,
                        o3: 40.285,
                        _id: ObjectId("646efee320e52ccbfd5ef937")
                  },
                  max: {
                        timestamp: ISODate("2023-05-22T10:13:00.000Z"),
                        cowe: 300.939,
                        pmnp10: 14,
                        hum: 62.3,
                        pmnp50: 0,
                        pmspug10: 3,
                        pm10: 60.37,
                        calpm10: 'test',
                        no2ae: 397.264,
                        calco: '',
                        batt: 100,
                        temp: 24,
                        pmnp05: 166,
                        co: 0.583,
                        pmspug100: 4,
                        pmaeug10: 3,
                        no2: 50.284,
                        vafe: 262.362,
                        pmnp100: 0,
                        pmaeug25: 4,
                        no2o3ae: 409.052,
                        calpm2_5: 'test',
                        pmaeug100: 4,
                        no2we: 109.925,
                        pm25: 198.91,
                        no2o3we: 422.077,
                        calno2: '',
                        pmnp03: 537,
                        calo3: 'test',
                        pmnp25: 4,
                        pmspug25: 4,
                        coae: 160.378,
                        pmdiag: 0,
                        o3: 40.285,
                        _id: ObjectId("646eff3220e52ccbfd5ef939")
                  }
            },
            meta: {
                  idafe: 'AirH358',
                  lbllocation: 'Poli-TO-1',
                  location: {
                        coordinates: [15.047446452336533, 37.36084832653023],
                        type: 'Point'
                  },
                  schemaver: 1,
                  verfmw: 'AH_1.3.1'
            },
            data: {
                  vafe: {
                        '0': 262.362,
                        '1': 262.362
                  },
                  no2we: {
                        '0': 109.925,
                        '1': 109.925
                  },
                  cowe: {
                        '0': 201.939,
                        '1': 300.939
                  },
                  no2: {
                        '0': 50.284,
                        '1': 50.284
                  },
                  coae: {
                        '0': 160.378,
                        '1': 160.378
                  },
                  calco: {
                        '0': '',
                        '1': ''
                  },
                  temp: {
                        '0': 24,
                        '1': 24
                  },
                  pmnp10: {
                        '0': 14,
                        '1': 14
                  },
                  timestamp: {
                        '0': ISODate("2023-05-22T10:12:00.000Z"),
                        '1': ISODate("2023-05-22T10:13:00.000Z")
                  },
                  no2o3ae: {
                        '0': 409.052,
                        '1': 409.052
                  },
                  calno2: {
                        '0': '',
                        '1': ''
                  },
                  pmaeug100: {
                        '0': 4,
                        '1': 4
                  },
                  o3: {
                        '0': 40.285,
                        '1': 40.285
                  },
                  pmnp03: {
                        '0': 537,
                        '1': 537
                  },
                  hum: {
                        '0': 62.3,
                        '1': 62.3
                  },
                  pm25: {
                        '0': 198.91,
                        '1': 198.91
                  },
                  _id: {
                        '0': ObjectId("646efee320e52ccbfd5ef937"),
                        '1': ObjectId("646eff3220e52ccbfd5ef939")
                  },
                  pmdiag: {
                        '0': 0,
                        '1': 0
                  },
                  pmnp100: {
                        '0': 0,
                        '1': 0
                  },
                  no2ae: {
                        '0': 397.264,
                        '1': 397.264
                  },
                  pmaeug25: {
                        '0': 4,
                        '1': 4
                  },
                  pmspug25: {
                        '0': 4,
                        '1': 4
                  },
                  pmspug100: {
                        '0': 4,
                        '1': 4
                  },
                  pmaeug10: {
                        '0': 3,
                        '1': 3
                  },
                  batt: {
                        '0': 100,
                        '1': 100
                  },
                  pmnp50: {
                        '0': 0,
                        '1': 0
                  },
                  pm10: {
                        '0': 60.37,
                        '1': 60.37
                  },
                  calpm2_5: {
                        '0': '',
                        '1': 'test'
                  },
                  pmnp05: {
                        '0': 166,
                        '1': 166
                  },
                  pmnp25: {
                        '0': 4,
                        '1': 4
                  },
                  calpm10: {
                        '0': '',
                        '1': 'test'
                  },
                  no2o3we: {
                        '0': 422.077,
                        '1': 422.077
                  },
                  co: {
                        '0': 0.583,
                        '1': 0.583
                  },
                  pmspug10: {
                        '0': 3,
                        '1': 3
                  },
                  calo3: {
                        '0': '',
                        '1': 'test'
                  }
            }
      }

I would like to be able some sensor readings by timestamp or index, i.e. delete readings having index 1 or readings whose timestamp is greater than a set timestamp, but I’m not able to figure out how to design such a type of query given this specific schema.
Any suggestion/example?
Thanks!

#3

Hello @Sergio_Ferlito1,

Thank you for reaching out to the MongoDB Community forums!

Could you please share a sample document from the ts1 collection? It is highly recommended to work with the actual time series collection rather than querying the “internal system bucket” collection.

Note: The internal system bucket documents are for internal use only, and they might change without notice. Note that this has already taken place, where in some cases, the data in the system.bucket collection is compressed. To read further, please refer to the Time Series Compression documentation.

Regarding the statement to delete readings with index 1, could you please provide some additional information or examples? This will help us understand your requirements better and help you.

Could you please provide further clarification regarding the need for deletion, and can it be accomplished using the $match operator?

Also, it’s worth noting that starting in MongoDB 5.1, you can perform some delete and update operations. For example, delete commands can only match the metaField field values, and you need to ensure that the command does not limit the number of documents to be deleted. You can set justOne: false or use the deleteMany() method. For more details, please refer to the Updates and Deletes documentation.

Best regards,
Kushagra

#4

Below is an example document from the TS collection:

{ timestamp: 2023-05-22T10:11:00.000Z,
  meta: 
   { idafe: 'AirH358',
     lbllocation: 'Poli-TO-1',
     location: 
      { coordinates: [ 15.047446452336533, 37.36084832653023 ],
        type: 'Point' },
     schemaver: 1,
     verfmw: 'AH_1.3.1' },
  cowe: 201.939,
  pmnp10: 14,
  hum: 62.3,
  pmnp50: 0,
  pmspug10: 3,
  pm10: 60.37,
  calpm10: '',
  no2ae: 397.264,
  calco: '',
  batt: 100,
  temp: 24,
  pmnp05: 166,
  co: 0.583,
  pmspug100: 4,
  _id: ObjectId("646b2461fdca2cffecec33c0"),
  pmaeug10: 3,
  no2: 50.284,
  vafe: 262.362,
  pmnp100: 0,
  pmaeug25: 4,
  no2o3ae: 409.052,
  calpm2_5: '',
  pmaeug100: 4,
  no2we: 109.925,
  pm25: 198.91,
  no2o3we: 422.077,
  calno2: '',
  pmnp03: 537,
  calo3: '',
  pmnp25: 4,
  pmspug25: 4,
  coae: 160.378,
  pmdiag: 0,
  o3: 40.285 }

I would like to delete a portion of data, either from a specific timestamp (not in the meta field so not possible to act directly on TS collection) or, acting on the internal bucket, using, for example, key ‘1’ in all sub-documents of the data field.
The actual limitations on TS collection (no change streams support and very limited support for delete/update) are, in my opinion, too strong, if I can’t either operate on internal bucket collection to obtain what I intended, the TS collection, although very useful for inserting data, are not very useful, or at least difficult to use, if I what change them after inserting.

#5

Hey @Sergio_Ferlito1,

As of MongoDB 6.0.6, this is not supported. However, the feature is planned and I believe it will be released in a future MongoDB version. For example, see SERVER-73285 for more info.

Regards,
Kushagra