Fetch distinct record based on values for that record with MongoDB

Question

I have collection of restaurant record .Some of restaurant in this collection belongs to some group(chain type restaurant eg. KfC etc) while other doesn't has any group(Individual restaurant ,that doesn't belongs to any chain).

example :

Restaurant collections

{_id:"1",title:"rest1",address:"somethingx",chain_id:"123"},
{_id:"2",title:"rest2",address:"somethingx",chain_id:"123"},
{_id:"3",title:"rest3",address:"somethingy",chain_id:"0"},
{_id:"4",title:"rest4",address:"somethingx",chain_id:"0"} 

Chain collection :

{_id:"123",chain_name:"VSWEETS",address_deatils:[
                                          {restID:"",address:"somethingx"},
                                          {restID:"",address:"somethingx"}
                                         ]
}

{_id:"456",chain_name:"KFC",address_deatils:[]}

I need to fetch distinct restaurant with similiar chain_id, i.e, only single restaurant should come if it belongs to some chain(chain_id !=0)

Answer 1

You could use the aggregation framework for this. The aggregation pipeline would have the first step as $match operator that filters the restaurants on the address. The $group pipeline stage will then group the filtered documents by the chain_id key and applies the accumulator expression $first to the $$ROOT system variable on each group. You can the reshape the documents using the $project pipeline stage.

The final aggregation pipeline that gives you the desired results follows:

db.restaurant.aggregate([
    {
        "$match": { "address" : "somethingx" }
    },
    {
        "$group": {
            "_id": "$chain_id",
            "data": { "$first": "$$ROOT" }
        }
    },
    {
        "$project": {
            "_id" : "$data._id",
            "title" : "$data.title",
            "address" : "$data.address",
            "chain_id" : "$data.chain_id"
        }
    }
])

Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : "4",
            "title" : "rest4",
            "address" : "somethingx",
            "chain_id" : "0"
        }, 
        {
            "_id" : "1",
            "title" : "rest1",
            "address" : "somethingx",
            "chain_id" : "123"
        }
    ],
    "ok" : 1
}