Skip to content

Instantly share code, notes, and snippets.

@marantz
Last active August 29, 2015 14:10
Show Gist options
  • Save marantz/d90c0f982e6d667d2ae2 to your computer and use it in GitHub Desktop.
Save marantz/d90c0f982e6d667d2ae2 to your computer and use it in GitHub Desktop.
MongoDB Array Distinct Count
db.test.aggregate(
[{ $unwind : "$history" }
,{ $match : { "key_type" : "MAC"} }
,{ $group : { _id:{"key":"$key","USER_ID":"$history.USER_ID"}, USER_COUNT:{$addToSet:1} } }
,{ $unwind :"$USER_COUNT" }
,{ $group : { _id:"$_id.key", count:{"$sum":1} } }]
);
@marantz
Copy link
Author

marantz commented Dec 3, 2014

TEST DATA

{
    "key" : "77-AF-9A-FA-B6-F3",
    "key_type" : "MAC",
    "history" : [
        {
            "I_DATETIME" : "20141103011530",
            "USER_ID" : "test1"
        },
        {
            "I_DATETIME" : "20141103011529",
            "USER_ID" : "test2"
        },
        {
            "I_DATETIME" : "20141103011528",
            "USER_ID" : "test3"
        },
        {
            "I_DATETIME" : "20141103011527",
            "USER_ID" : "test2"
        },
        {
            "I_DATETIME" : "20141103011526",
            "USER_ID" : "test2"
        }
    ]
}

RESULT EXAMPLE

{
    "result" : [ 
        {
            "_id" : "22-DF-9A-FC-B6-D3",
            "count" : 3
        }, 
        {
            "_id" : "D0-DF-9A-FC-EE-9B",
            "count" : 1
        }, 
        {
            "_id" : "D0-DF-9A-FC-B6-D3",
            "count" : 1
        }, 
        {
            "_id" : "10-1F-74-E9-57-CC",
            "count" : 1
        }, 
        {
            "_id" : "333",
            "count" : 1
        }, 
        {
            "_id" : "00-50-56-C0-00-08",
            "count" : 1
        }
    ],
    "ok" : 1
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment