CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
raj
raj

Reputation: 388

Use only limited keys from $lookup aggregation

I want to filter data after $lookup aggregation.

Now, I want the only value of location key from child collection, not the whole document. Also if I need the specific key in location i.e zone_id What should I do? Please help.

I am using below query

// Query

db.parent.aggregate([
        {
          $lookup:
        {
          from: "child",
          localField: "pid",
          foreignField: "pid",
          as: "more"
        } 
       }
])

// Data // Child collection

 db.child.insert({
  "pid": 1,
  "name": "Max",
  "title": "Top" 
  "status": 1,

  "description": {
    "destination": "jur bagh",
    "community": "abc"
  },
  "location:": {
    "zone_id": "north",
    "city": "jaipur",
    "latitude": "12.121212",
    "longitude": "21.414134"
  }, 
  "created_by": "user_id",
  "modified_by": "user",
  "created_at": "12:00",
  "updated_at": "13:00"
});

// parent collection

 db.parent.insert({
  "pid": 1,
  "pname": "PQW",
  "rox": "Labs",
  "status": 1,
  "created_by": "smdcd",
  "modified_by": "pink",
  "created_at": "12:00",
  "updated_at": "13:00"
});

I want result like this 

db.parent.insert({
  "pid": 1,
  "pname": "PQW",
  "rox": "Labs",
  "status": 1,
  "created_by": "smdcd",
  "modified_by": "pink",
  "created_at": "12:00",
  "updated_at": "13:00"
"more" [
"location:": {
    "zone_id": "north",
    "city": "jaipur",
    "latitude": "12.121212",
    "longitude": "21.414134"
  }
]


});

Upvotes: 1

Views: 1416

Answers (2)

Neil Lunn
Neil Lunn

Reputation: 151112

You want $arrayElemAt here to reference the "single" result from the $lookup and place your new field in the document using $addFields where available or otherwise $project with all fields:

db.parent.aggregate([
  { "$lookup": {
    "from": "child",
    "localField": "pid",
    "foreignField": "pid",
    "as": "location"  
  }},
  { "$addFields": {
      "location": { "$arrayElemAt": [ "$location.location:", 0 ] }
  }}
])

Also note that you seem to have a typo in your field name in the child since it is called: "location:" with the colon : on the end. Or maybe that's a mistake in just the question.

Produces:

{
    "_id" : ObjectId("5968821f7dcd6a5f6a9b4b7d"),
    "pid" : 1.0,
    "pname" : "PQW",
    "rox" : "Labs",
    "status" : 1.0,
    "created_by" : "smdcd",
    "modified_by" : "pink",
    "created_at" : "12:00",
    "updated_at" : "13:00",
    "location" : {
        "zone_id" : "north",
        "city" : "jaipur",
        "latitude" : "12.121212",
        "longitude" : "21.414134"
    }
}

Based on the data as provided in your question.

Alternately process with $map if you do intend multiple results:

db.parent.aggregate([
  { "$lookup": {
    "from": "child",
    "localField": "pid",
    "foreignField": "pid",
    "as": "more"  
  }},
  { "$addFields": {
    "more": {
      "$map": {
        "input": "$more.location:",
        "as": "l",
        "in": { "location": "$$l" }
      }
    }
  }}
])

With results like:

{
    "_id" : ObjectId("5968821f7dcd6a5f6a9b4b7d"),
    "pid" : 1.0,
    "pname" : "PQW",
    "rox" : "Labs",
    "status" : 1.0,
    "created_by" : "smdcd",
    "modified_by" : "pink",
    "created_at" : "12:00",
    "updated_at" : "13:00",
    "more" : [ 
        {
            "location" : {
                "zone_id" : "north",
                "city" : "jaipur",
                "latitude" : "12.121212",
                "longitude" : "21.414134"
            }
        }
    ]
}

Upvotes: 1

Pav K.
Pav K.

Reputation: 2858

Use $project https://docs.mongodb.com/manual/reference/operator/aggregation/project/

,{$project:{
  pid:1,
  pname:1,
  rox:1,
  status:1,
  modified_by:1,
  created_by:1,
  created_at:1,
  updated_at:1,
  more.location:$more.location
}}
// 'name of the field':1 means you want to keep the 'name'.
//  more.location:$more.location means you passing down the data you need.

To filter down the results you can add $match into your aggregate https://docs.mongodb.com/manual/reference/operator/aggregation/match/

,{$match:{
    more.location.zone_id:'north'
}}

if you have multiple locations as array but only want to return one, you might want to $unwind array first:

,{$unwind:'$more.location'}

Upvotes: 0

Related Questions