CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
Breno Oliveira
Breno Oliveira

Reputation: 53

MongoDB: Aggregate with nested $group

I have the following query:

 db.test.aggregate([
    {
        $match: {
            'type': 'energy'
        }
    },
    {
        $limit: 10000
    },
    {
        $addFields: {
            day: {
                $dateToString: {
                    date: "$when.date",
                    format: "%d/%m/%Y"
                }
            },
            sensor: "$id"
        }, 
    }, 
    {
        $project: {
            _id: 1,
            sensor: 1,
            when: 1,
            value: 1,
            day: 1
        }
    },    
    {
        $group: {
            _id: "$day",
            data: {
                $push: "$$ROOT"
            },

        }
    },
    {
        $sort: {
            'data': 1
        }
    }
])

Which returns this data format:

 {
    "_id" : "05/04/2018",
    "data" : [
        {
            "_id" : ObjectId("5ac66be9b02d5c18fd4106c7"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-05T13:53:22.703-03:00"),
                "unix" : 1522947202,
                "milli" : 1522947202703
            },
            "day" : "05/04/2018",
            "sensor" : "sen3"
        },
        {
            "_id" : ObjectId("5ac66be9b02d5c18fd4106c8"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-05T13:53:22.705-03:00"),
                "unix" : 1522947202,
                "milli" : 1522947202705
            },
            "day" : "05/04/2018",
            "sensor" : "sen4"
        }
    ]
},
{
    "_id" : "06/04/2018",
    "data" : [
        {
            "_id" : ObjectId("5ac7e5d2efe88a4e76c008d2"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-06T18:25:38.885-03:00"),
                "unix" : 1523049938,
                "milli" : 1523049938885
            },
            "day" : "06/04/2018",
            "sensor" : "sen3"
        },
        {
            "_id" : ObjectId("5ac7e5e4efe88a4e76c008d5"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-06T18:25:56.105-03:00"),
                "unix" : 1523049956,
                "milli" : 1523049956105
            },
            "day" : "06/04/2018",
            "sensor" : "sen3"
       }
    ]
},



...

Note that we have in each "data" document, different types of sensors (sen3, sen4, ... ,senN).

I'm trying to aggregate this result once more, agrouping the data by sensor, to get the output something like this:

  {
    "_id" : "05/04/2018",
    "sen3" : [
        {
            "_id" : ObjectId("5ac66be9b02d5c18fd4106c7"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-05T13:53:22.703-03:00"),
                "unix" : 1522947202,
                "milli" : 1522947202703
            },
            "day" : "05/04/2018",
            "sensor" : "sen3"
        },
        {
            "_id" : ObjectId("5ac66be9b02d5c18fd4106c7"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-05T13:53:22.703-03:00"),
                "unix" : 1522947202,
                "milli" : 1522947202703
            },
            "day" : "05/04/2018",
            "sensor" : "sen3"
        }
    ],
    "sen4" : [
        {
            "_id" : ObjectId("5ac66be9b02d5c18fd4106c8"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-05T13:53:22.705-03:00"),
                "unix" : 1522947202,
                "milli" : 1522947202705
            },
            "day" : "05/04/2018",
            "sensor" : "sen4"
        },
        {
            "_id" : ObjectId("5ac66be9b02d5c18fd4106c8"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-05T13:53:22.705-03:00"),
                "unix" : 1522947202,
                "milli" : 1522947202705
            },
            "day" : "05/04/2018",
            "sensor" : "sen4"
        }
    ]
},
{
    "_id" : "06/04/2018",
    "sen3" : [
        {
            "_id" : ObjectId("5ac7e5d2efe88a4e76c008d2"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-06T18:25:38.885-03:00"),
                "unix" : 1523049938,
                "milli" : 1523049938885
            },
            "day" : "06/04/2018",
            "sensor" : "sen3"
        },
        {
            "_id" : ObjectId("5ac7e5e4efe88a4e76c008d5"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-06T18:25:56.105-03:00"),
                "unix" : 1523049956,
                "milli" : 1523049956105
            },
            "day" : "06/04/2018",
            "sensor" : "sen3"
        }
    ],
    "sen4": [
        {
            "_id" : ObjectId("5ac7e7a7efe88a4e76c008de"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-06T18:33:27.365-03:00"),
                "unix" : 1523050407,
                "milli" : 1523050407365
            },
            "day" : "06/04/2018",
            "sensor" : "sen4"
        },
        {
            "_id" : ObjectId("5ac7e7a7efe88a4e76c008de"),
            "value" : 0,
            "when" : {
                "date" : ISODate("2018-04-06T18:33:27.365-03:00"),
                "unix" : 1523050407,
                "milli" : 1523050407365
            },
            "day" : "06/04/2018",
            "sensor" : "sen4"
        }
    ]
}

In short: I want to group the data in days, sensors and inside each sensor the results that belongs with that day and sensor.

I'm trying to create a nested $group but in all tentatives it goes wrong.

Is this possible to do and case yes, how?

Upvotes: 4

Views: 3313

Answers (1)

Ashh
Ashh

Reputation: 46441

You can try below aggregation in mongodb 3.6 and above

db.collection.aggregate([
  { "$match": { "type": "energy" }},
  { "$limit": 10000 },
  { "$addFields": {
    "day": { "$dateToString": { "date": "$when.date", "format": "%d/%m/%Y" }},
    "sensor": "$id"
  }}, 
  { "$project": { "_id": 1, "sensor": 1, "when": 1, "value": 1, "day": 1 }},
  { "$group": {
    "_id": { "day": "$day", "sensor": "$sensor" },
    "data": { "$push": "$$ROOT" }
  }},
  { "$group": {
    "_id": { "day": "$_id.day" },
    "data": { "$push": { "k": "$_id.sensor", "v": "$data" }}
  }},
  { "$addFields": { "data": { "$arrayToObject": "$data" }}},
  { "$replaceRoot": { "newRoot": { "$mergeObjects": [ "$_id", "$data" ] }}}
])

Upvotes: 5

Related Questions