CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
FRizal
FRizal

Reputation: 448

Select a field in an array inside mongodb and display only unique field

Will need select a field in an array inside mongodb. 

{
"_id" : ObjectId("53dbb05fa976627439d43884"),
"employee" : [ 
    {
        "dateJoin" : "1986-03-10",
        "deptName" : "x",

    }, 
    {
        "dateJoin" : "1986-12-11",
        "deptName" : "y",

    },
 {
        "dateJoin" : "1986-12-11",
        "deptName" : "y",

    }
 ]
 }

In my case I would like select all the unique deptName available. The output is something like below.

  { deptName:x,
   deptName:y }

So far I have tried the below command but no luck.

db.employee.find( {},{ employee.deptName:1})

Upvotes: 1

Views: 1970

Answers (2)

Neil Lunn
Neil Lunn

Reputation: 151072

The aggregation framework is probably your way, but if you just wan the distinct values across the collection there is the .distinct() method:

db.collection.distinct("employee.deptName")

Which outputs :

 ["x","y"]

Alternately with the aggregation framework just do:

db.collection.aggregate([
    { "$unwind": "$employee" },
    { "$group": { "_id": "employee.deptName" } }
])

Which outputs:

{ "_id" : "y" }
{ "_id" : "x" }

Or the same thing as a single document result:

db.collection.aggregate([
    { "$unwind": "$employee" },
    { "$group": { 
        "_id": null,
        "deptName": { "$addToSet": "$employee.deptName" }
    }}
])

Which outputs:

{ "_id" : null, "deptName" : [ "y", "x" ] }

Or if you actually want this per document, then like this:

db.collection.aggregate([
    { "$unwind": "$employee" },
    { "$group": { 
        "_id": "$_id",
        "deptName": { "$addToSet": "$employee.deptName" }
    }}
])

For this:

{ "_id" : ObjectId("53dbb05fa976627439d43884"), "deptName" : [ "y", "x" ] }

Upvotes: 1

throrin19
throrin19

Reputation: 18197

Ok, on your case, this is not a simple query but an aggregation query. If I remember that : 

db.employee.aggregate([
    {
        $unwind : '$employee'
    }
    {
        $group : {
            _id : {
                id : '$_id',
                deptName : '$employee.deptName'
            }
        }
    },
    {
        $project : {
            _id : '$_id.id',
            deptName : '$_id.deptName'
        }
    },
    {
        $group : {
            _id : '$_id',
            deptName : { $addToSet : '$deptName' }
        }
    }
]);

unwind is used to unwind your array and generate one record per array items. group is used to group by _id and depName project is used to reorganize out record Line group is used this time to regroup all records and add into An Array all uniqe deptName.

After that you shoud retrive this for all records : 

{ 
    _id : ObjectId("53dbb05fa976627439d43884"),
    deptName : [ 'x', 'y' ]
}

I have not test this aggregate function but, if i remember, it should be works.

Edit : I test that and it works fine

For aggregation, the result is like this : 

{
    "result" : [ 
        {
            "_id" : ObjectId("54082a2463a023dbfa7d82fb"),
            "deptName" : [ 
                "x", 
                "y"
            ]
        }
    ],
    "ok" : 1
}

Upvotes: 0

Related Questions