Robmongo - aggregate values distinct by other value\cloumn

Question

I'm new to robmongo and I received an assignment to write some queries.

let say I have a collection that each key has some values for example value of "userId" and value of "deviceModel". I need to write a query that shows for each device model how many users has this device. this is what I got so far:

db.device_data.aggregate([ {"$group" : {_id:"$data.deviceModel", count:{$sum:1}}}])

The problem is that this aggregate for each device the number of keys it appears.

{
"_id" : { "$binary" : "AN6GmE7Thi+Sd/dpLRjIilgsV/4AAAg=", "$type" : "00" },
"auditVersion" : "1.0",
"currentTime" : NumberLong(1479301118381),
"data" : {
    "deviceDesign" : "bullhead",
    "loginType" : "GOOGLE",
    "source" : "SDKLoader",
    "systemUptimeMillis" : 137652880.0,
    "simCountryIso" : "il",
    "networkOperatorName" : "Cellcom",
    "hasPhonePermission" : true,
    "deviceIdentifier" : "353627074839559",
    "sdkVersion" : "0.7.939.2016-11-14.masterDev",
    "brand" : "google",
    "osVersion" : "7.0",
    "osVersionIncremental" : "3239497",
    "deviceModel" : "Nexus 5X",
    "deviceSDKVersion" : 24.0,
    "manufacturer" : "LGE",
    "sdkShortBuildDate" : "2016-11-14",
    "sdkFullBuildDate" : "Mon Nov 14 22:16:40 IST 2016",
    "product" : "bullhead"
},
"timezone" : "Asia/Jerusalem",
"collectionAlias" : "DEVICE_DATA",
"shortDate" : 17121,
"userId" : "00DE86984ED3862F9277F7692D18C88A@1927cc81cfcf7a467e9d4f4ac7a1534b"}

this is an example of how one key locks like.

Show your sample document. Change the sum : 1 to $sum : "$userid"... if I got your doc structure correctly.. — notionquest, Mar 20 '17 at 19:13
Try to check this http://stackoverflow.com/questions/22932364/mongodb-group-values-by-multiple-fields — Maksim Kudimov, Mar 20 '17 at 20:02

notionquest · Accepted Answer · 2017-03-20T20:42:28.403

The below query should give you distinct count of userId for a deviceModel. I meant if a same userId present for a deviceModel multiple items, it will be counted only once.

db.collection.aggregate([ {"$group" : {_id:"$data.deviceModel", userIds:{$addToSet: "$userId"}}
},
{
    $unwind:"$userIds"
},
{
    $group: { _id: "$_id", userIdCount: { $sum:1} }
}])

Unwind:-

Deconstructs an array field from the input documents to output a document for each element.

In the above solution, it deconstructs the userId array formed on the first pipeline.

addToSet:-

Returns an array of all unique values that results from applying an expression to each document in a group of documents that share the same group by key.

This function ensures that only unique values are added to an array. In the above case, the userId is added to an array in the first pipeline.

First of all, thanks! can you give a short explanation of what unwind and addToSet does? — yairabr, Mar 20 '17 at 20:16

Robmongo - aggregate values distinct by other value\cloumn

1 Answers1