Skip to content

Instantly share code, notes, and snippets.

@ghstahl
Last active November 19, 2023 13:41
Show Gist options
  • Save ghstahl/9279b2ad808752447902db09e77c98a4 to your computer and use it in GitHub Desktop.
Save ghstahl/9279b2ad808752447902db09e77c98a4 to your computer and use it in GitHub Desktop.
Mongo Timeseries w/ bucket ID

Mongo Timeseries w/ Bucket ID

BEWARE: You can't have a uniqueness index on a timeseries collection. If you write usage records that MUST be unique, they must be taken care of in the aggregation.

stackoverflow
mongodb timeseries issue

Which then whats the point of using a timeseries DB for normal queries if there are duplicates.

go.migrate

Create Collection

Up

[
  {
      "create": "usage",
      "timeseries": {
          "timeField": "date",
          "metaField": "meta",
          "granularity": "minutes"
      }
  }
]

Down

[
  {
      "drop": "usage"
  }
]

Indexes

Up

[{
  "createIndexes": "usage",
  "indexes": [
    {
      "key": {
        "meta.org_id" :1,
        "meta.bucket_id" : 1,
        "date" : 1
      },
      "name" : "meta.org_id_1_meta.bucket_id_1_date_1",
      "unique": false,
      "background": true,
      "collation":{
          "locale":"en_US",
          "strength": 2
      }
    }
  ]
}]

Down

[
  {
    "dropIndexes": "usage",
    "index": "meta.org_id_1_meta.bucket_id_1_date_1"
  }
]

Insert Data

db.getCollection("usage").insert([
{
    date: ISODate("2020-01-03T05:00:00.000Z"),
    count: 5,
    meta:{org_id:"ORG1234a",bucket_id:"ABCD"},
},
{
    date: ISODate("2020-01-03T05:00:01.000Z"),
    count: 6,
    meta:{org_id:"ORG1234a",bucket_id:"ABCD"},
}])

Aggregate

db.getCollection("usage").aggregate([
{
    $match: {
        "meta.org_id": "ORG1234a",
        "meta.bucket_id": "ABCD"
    }
},
{
    $group: 
    {
        _id: "$meta.org_id",
        total: {
            $sum: "$count"
        }
    }
}
])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment