How to prevent duplicates in mongodb timeseries collection

Jordan_Baczuk · 2021-10-13T21:56:45.658Z

Problem

Sensors check-in periodically, but network connectivity issues may cause them to check-in with the same data more than once.

MongoDB does not allow the unique property on secondary indexes for timeseries collections (MongoDB 5.0). Timeseries Limitations

In addition, calculations need to be done on the data (preferably using aggregations) that involve counting the number of entries, which will be inaccurate if there are duplicates. Not to mention it bloats the database and is just messy.

Question

Is there any way to prevent duplicate entries in a MongoDB Timeseries collection?

Darren_Haba · 2021-11-26T17:05:20.264Z

I’m wondering the same thing, has anyone found a solution?

steevej · 2021-11-29T14:55:14.441Z

Can’t you use an update with upsert:true?

Something along the way:

// Usual inserted document that produces duplicates
insert = { "sensor":1 ,
    "timestamp":2 ,
    "mesure":"pressure" ,
    "value":10 }

// Modified into a query that would define you uniqueness
query = { "sensor" : 1 , "timestamp" : 2 , "mesure" : "pressure" }

// With the value
value = { "value":10 }

// Also replace the following insert that produces a new duplicate
// every time it is called
c.insert( insert ) ;

// into an update with upsert:true that will insert only once
// no matter how often it is called.
c.update( query , value , { upsert:true } )

I am not yet familiar with the new time series collections. I do not know if it is applicable or not. But I feel it could.

Gorkem_Erdogan · 2021-12-01T19:34:31.637Z

I’m having the same issue here. Any solutions other than upsert?

steevej · 2021-12-01T21:30:53.758Z

Gorkem_Erdogan:

Any solutions other than upsert?

What is wrong with upsert?

Gorkem_Erdogan · 2021-12-02T05:37:01.136Z

Hello man,

Well I dont think time series collections allow us to use update operations. It is append only.

steevej · 2021-12-02T14:28:05.101Z

Insert only would make sense in order to improve performance of the main use‑case for time series.

But it would prevent some others where you would want to update your time series entries with some post inserting data.

This simply confirms that

steevej:

I am not yet familiar with the new time series collections.

and that I must start learning more.

sergeimeza · 2021-12-02T15:25:08.499Z

While you cannot prevent entries with duplicated data. You can query/aggregate the data in such a way that replicated data is ignored.

Below is some aggregation pipeline on the some_timeseries Time Series collection

db.some_timeseries.aggregate([
  // ...
  {
    $group: {
      _id: {
        username: '$metadata.username',
        media_id: '$metadata.media_id',
        timestamp: {
          $dateTrunc: {
            date: '$timestamp',
            unit: 'day',
            startOfWeek: 'monday',
            binSize: 1,
          },
        },
      },
      // ...
    },
  },
  ///
])

sergeimeza · 2021-12-02T15:26:42.511Z

On the same $group stage you could pick up the max, min, avg etc for a given entry.

steevej · 2021-12-02T15:26:51.180Z

Gorkem_Erdogan:

Well I dont think time series collections allow us to use update operations.

You are absolutely right. I should have followed the link provided in the first post (https://docs.mongodb.com/manual/core/timeseries/timeseries-limitations/)
and I would have read:

Time series collections only support insert operations and read queries. Updates and manual delete operations result in an error.

Darren_Haba · 2021-12-03T03:23:04.204Z

It appears that the limitations imposed on time-series collections are temporary because in the upcoming version MongoDB 5.1, we can perform some delete and update operations. I’m guessing time-series might support upsert and the unique property on secondary indexes in future releases.

I came up with my own method to ensure data is never retrieved from an API and accidentally inserted twice, which was my original problem.

I’m still learning the aggregation pipeline, but @sergeimeza way of creating a query that ignores duplicates is probably the best solution for now.

Thanks for all the help.

Hector_Valverde · 2022-08-12T10:12:01.591Z

Hi there,

I was hoping to see the support if the Unique indexes in time series but apparently it is not there yet: https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations/#std-label-timeseries-limitations-secondary-indexes

The $group solution looks like a workaround, it doesn’t really avoid duplicates and adds constraints to the clients who acess the collection.

Anyone has any other suggestion?

Michael_Gillhofer · 2024-12-18T17:29:41.437Z

This seems to be an issue still.

Any Ideas on how to solve this in the year 2025?

Vishal_Chauhan1 · 2025-03-07T07:05:26.060Z

Hello everyone,

Anyone find a better solution for this issue?