How to get count of duplicate keys using mongodb?

Question

I have following Json structure-

[{
 cName:"A",
"datastores" : [ 
    {
        "status":"unused"
    }, 
    {
        "name" : "datastore1",
         "status":"used"
    }, 
    {
        "name" : "onNetApp7m",
         "status":"used"
    }, 
    {
        "name" : "datastore1",
         "status":"used"
    }
    ],
},
{
 cName:"B",
"datastores" : [ 
    {
        "name" : "dsn",
         "status":"used"
    }, 
    {
        "name" : "dsn",
         "status":"used"
    }, 
    {
        "name" : "datastore2",
         "status":"used"
    }
    ],
}
]

I want to find only such names from array/list "ds" whose count is greater than 1. I want following output-

[{
"cName":"A",
"name" : "datastore1",
"count": 2
},
{
"cName":"B",
"name" : "dsn",
"count": 2
}]

Currently, for getting count, I am handling it in my code logic. Is it possible to get count from query itself using mongo??

ds has been changed to datastores - was it a typo?

BatScream
– BatScream

2014-12-05 08:41:16 +00:00
Commented Dec 5, 2014 at 8:41 — BatScream
– BatScream, Commented Dec 5, 2014 at 8:41
@BatScream yes it is..

Vishwas
– Vishwas

2014-12-05 08:52:53 +00:00
Commented Dec 5, 2014 at 8:52 — Vishwas
– Vishwas, Commented Dec 5, 2014 at 8:52

BatScream · Accepted Answer · 2014-12-05 08:35:15Z

1

You need to aggregate as below:

Unwind the datastores array.
Group the records by the cName and the datastores name fields. Get the count of records in each group.
The group we need to match is now the group having count > 1.
Project the required fields.

Code:

db.collection.aggregate([
{$unwind:"$datastores"},
{$group:{"_id":{"cName":"$cName","name":"$datastores.name"},"count":{$sum:1}}},
{$match:{"count":{$gt:1}}},
{$project:{"_id":0,"cName":"$_id.cName","name":"$_id.name","count":1}}
])

answered Dec 5, 2014 at 8:35

BatScream

19.7k4 gold badges54 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get count of duplicate keys using mongodb?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related