Calculating unique fields in nested documents in MongoDB

Question

I have data in my collection as below. I want to show for every unique EndpointId, sum of all unique Tags.Uid defines that how many tags this endpoint has. and sum of (sum of every unique Sensors.Type for every unique Tags.Uid) In this case this Endpoint has 4 Tags and 16 Sensors.

{
    "_id" : ObjectId("5aef51dfaf42ea1b70d0c4db"),    
    "EndpointId" : "89799bcc-e86f-4c8a-b340-8b5ed53caf83",    
    "DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
    "Url" : "test",
    "Tags" : [ 
        {
            "Uid" : "C1:3D:CA:D4:45:11",
            "Type" : 1,
            "DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
            "Sensors" : [ 
                {
                    "Type" : 1,
                    "Value" : NumberDecimal("-95")
                }, 
                {
                    "Type" : 2,
                    "Value" : NumberDecimal("-59")
                }, 
                {
                    "Type" : 3,
                    "Value" : NumberDecimal("11.029802536740132")
                }, 
                {
                    "Type" : 4,
                    "Value" : NumberDecimal("27.25")
                }, 
                {
                    "Type" : 6,
                    "Value" : NumberDecimal("2924")
                }
            ]
        },         
        {
            "Uid" : "C1:3D:CA:D4:45:11",
            "Type" : 1,
            "DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
            "Sensors" : [ 
                {
                    "Type" : 1,
                    "Value" : NumberDecimal("-95")
                }, 
                {
                    "Type" : 2,
                    "Value" : NumberDecimal("-59")
                }, 
                {
                    "Type" : 3,
                    "Value" : NumberDecimal("11.413037961112279")
                }, 
                {
                    "Type" : 4,
                    "Value" : NumberDecimal("27.25")
                }, 
                {
                    "Type" : 6,
                    "Value" : NumberDecimal("2924")
                }
            ]
        },          
        {
            "Uid" : "E5:FA:2A:35:AF:DD",
            "Type" : 1,
            "DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
            "Sensors" : [ 
                {
                    "Type" : 1,
                    "Value" : NumberDecimal("-97")
                }, 
                {
                    "Type" : 2,
                    "Value" : NumberDecimal("-58")
                }, 
                {
                    "Type" : 3,
                    "Value" : NumberDecimal("10.171658037099185")
                }
            ]
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5aef51e0af42ea1b70d0c4dc"),    
    "EndpointId" : "89799bcc-e86f-4c8a-b340-8b5ed53caf83",    
    "Url" : "test",
    "Tags" : [ 
        {
            "Uid" : "E2:02:00:18:DA:40",
            "Type" : 1,
            "DateTime" : ISODate("2018-05-06T19:05:04.574Z"),
            "Sensors" : [ 
                {
                    "Type" : 1,
                    "Value" : NumberDecimal("-98")
                }, 
                {
                    "Type" : 2,
                    "Value" : NumberDecimal("-65")
                }, 
                {
                    "Type" : 3,
                    "Value" : NumberDecimal("7.845424441900629")
                }, 
                {
                    "Type" : 4,
                    "Value" : NumberDecimal("0.0")
                }, 
                {
                    "Type" : 6,
                    "Value" : NumberDecimal("3012")
                }
            ]
        }, 
        {
            "Uid" : "12:3B:6A:1A:B7:F9",
            "Type" : 1,
            "DateTime" : ISODate("2018-05-06T19:05:04.574Z"),
            "Sensors" : [ 
                {
                    "Type" : 1,
                    "Value" : NumberDecimal("-95")
                }, 
                {
                    "Type" : 2,
                    "Value" : NumberDecimal("-59")
                }, 
                {
                    "Type" : 3,
                    "Value" : NumberDecimal("12.939770381907275")
                }
            ]
        }
    ]
}

I have the query as below which counts something I want.

db.collection.aggregate([
  { "$unwind": "$Tags" },
  { "$unwind": "$Tags.Sensors" },
  { "$group": {
    "_id": {
      "EndpointId": "$EndpointId",
      "Uid": "$Tags.Uid",
      "Type": "$Tags.Sensors.Type"
    },
  }},
  { "$group": {
    "_id": {
      "EndpointId": "$_id.EndpointId",
      "Uid": "$_id.Uid",
    },
    "count": { "$sum": 1 }
  }},
  { "$group": {
    "_id": "$_id.EndpointId",
    "tagCount": { "$sum": 1 },
    "sensorCount": { "$sum": "$count" }
  }}
])

My question is how can I calculate count for every unique EndpointID (in this case this is 2) inside this query without any separate query?

Try using the `$addToSet` group operator in order to generate an array of unique elements, then retrieve a count of the elements. — B. Fleming, May 15 '18 at 22:25
Either you or someone else on your team has already asked the question. If it actually is you, then please don't create new accounts to ask questions. If it's someone on your team, please communicate so that you are not all asking the same things here. — Neil Lunn, May 16 '18 at 01:50
It wasn't me. This is the first time I am asking this question. I just wanted to add calculating the count of `EndpointId`s to this query. As I mentioned, I want to add a part to this query to calculate how many request came from each EndpointId. (for example, here is 2 for EndpointId "89799bcc-e86f-4c8a-b340-8b5ed53caf83") — Mhmd, May 16 '18 at 13:23

Calculating unique fields in nested documents in MongoDB

0 Answers0