Home > Software engineering >  Fetching records with same time_stamp but different identifiers
Fetching records with same time_stamp but different identifiers

Time:03-28

Current data:

    [
      {
        "_id": "6239bd7a465f8d1fbfc80f95",
        "tag_id": "620e23a6450b9ea1a1e31d75",
        "time_stamp": "2022-02-28T19:55:55.666000",
        "is_deleted": false,
        "value": 20
      },
      {
        "_id": "623ef6e4cd717dc11aee942b",
        "tag_id": "620e23a6450b9ea1a1e31d75",
        "time_stamp": "2022-03-01T20:55:55.666000",
        "is_deleted": false,
        "value": 20
      },
      {
        "_id": "623ef6f0cd717dc11aee94f6",
        "company_id_rdb": 3,
        "tag_id": "620e23a6450b9ea1a1e31d75",
        "time_stamp": "2022-03-02T21:55:55.666000",
        "is_deleted": false,
        "value": 20
      },
      {
        "_id": "623ef718cd717dc11aee97d9",
        "company_id_rdb": 3,
        "tag_id": "620e23a6450b9ea1a1e31da6",
        "time_stamp": "2022-03-02T19:55:55.666000",
        "is_deleted": false,
        "value": 20
      },
      {
        "_id": "623ef722cd717dc11aee9895",
        "tag_id": "620e23a6450b9ea1a1e31da6",
        "time_stamp": "2022-03-03T20:55:55.666000",
        "is_deleted": false,
        "value": 20
      },
      {
        "_id": "623ef72ccd717dc11aee9943",,
        "tag_id": "620e23a6450b9ea1a1e31da6",
        "time_stamp": "2022-03-04T21:55:55.666000",
        "is_deleted": false,
        "value": 20
      }
    ]

The final object should be like this:

      {
    
        "time_stamp": "2022-03-04T21:55:55.666000",
        "value": 60,
        "tags_count":2
      },
      {
        "time_stamp": "2022-02-28T19:55:55.666000",
        "tags_count":2,
        "value": 60
      },
      {
        "time_stamp": "2022-03-03T20:55:55.666000",
        "tags_count":2,
        "value": 60
      },

I tried the below query but had no luck:

db.getCollection('data').aggregate([
  {$match:{"tag_id":{$in:[ObjectId("620e23a6450b9ea1a1e31da6"),ObjectId("620e23a6450b9ea1a1e31d75")]}}},
  {$group:{"_id":"$time_stamp","value":{$sum:"$value"},"count":{$sum:1}}}
])

I have a collection that has records in it at a difference of 10 mins each for different tags(tag_id), and a value for each tag. I want to write a query in which I pass a list of tags let's say t1 and t2 and a time range (since $ until), and the query should return the sum of the value of the tags passed at every timestamp coming in that range.

CodePudding user response:

It looks you are almost there , maybe something like this:

db.collection.aggregate([
{
 $match: {
  "tag_id": {
    "$in": [
      "620e23a6450b9ea1a1e31da6",
      "620e23a6450b9ea1a1e31d75"
    ]
  },
  "time_stamp": {
    "$gte": "2022-02-28T19:55:55.666000",
    "$lte": "2022-03-03T20:55:55.666000"
  }
 }
},
{
  $group: {
    "_id": "$time_stamp",
    "value": {
      $sum: "$value"
    },
    "count": {
      $sum: 1
    }
   }
  }
])

Explained:

  1. Match by tag_id and time_stamp range
  2. Group by the time_stamp and sum values count

playground

  • Related