CODEWITHSUNDEEP
pythonmongodbaggregation-frameworkpymongo
Ninja Warrior 11
Ninja Warrior 11

Reputation: 372

Select fields to return from $lookup

I have a piece of code to join collection A (sample) to collection B (locators). I've tried the $unwind, $group and $push syntax the only problem is that I can't return the field locator and record.

data = db.sample.aggregate([
{'$lookup': {
    'from': 'locators',
    'localField': "locator",
    'foreignField': "_id",
    'as': "metalocator"}}])

print(list(data))

Which returns

[
  {
    '_id': '599A65E1A80541BA',
    'locator': 'ABC',
    'record': 'Nicaragua',
    'metalocator': [{'_id': 'ABC', 'group': 'Location', 'section': 'Geo', 'cabinet': 'Country', 'record': 'string', 'status': 'integer'}]
  },
  { 
    '_id': '428E970995AE8C76',
    'locator': 'CDE',
    'record': 'Nigeria',
    'metalocator': [{'_id': 'CDE', 'group': 'Location', 'section': 'Geo', 'cabinet': 'Country', 'record': 'string', 'status': 'integer'}]
  }
]

attempt 1

data = db.sample.aggregate([
    {"$lookup": {"from": "locators",
                 "localField": "locator",
                 "foreignField": "_id",
                 "as": "metalocator"}},
    {"$unwind": '$metalocator'},
    {"$group": {"_id": "$_id",
                "metalocator": {"$push":  {
                    "section": "$metalocator.section",
                    "cabinet": "$metalocator.cabinet"}}}}
])
print(list(data))

Which returns:

[
  {
    '_id': '1835853D2982AAEF',
    'metalocator': [{'section': 'Geo', 'cabinet': 'Country'}]
  },
  {
    '_id': '428E970995AE8C76',
    'metalocator': [{'section': 'Geo', 'cabinet': 'Country'}]
  }
]

Expected result should be:

[
  {
    '_id': '1835853D2982AAEF',
    'locator': 'ABC',
    'record': 'Nicaragua',
    'metalocator': [{'section': 'Geo', 'cabinet': 'Country'}]
  },
  {
    '_id': '428E970995AE8C76',
    'locator': 'CDE',
    'record': 'Nigeria',
    'metalocator': [{'section': 'Geo', 'cabinet': 'Country'}]
  }
]

Upvotes: 5

Views: 4286

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

You want $map:

db.sample.aggregate([
  {'$lookup': {
    'from': 'locators',
    'localField': "locator",
    'foreignField': "_id",
    'as': "metalocator"
  }},
  { '$addFields': {
    'metalocator': {
      '$map': {
        'input': '$metalocator',
        'in': {
          'section': '$$this.section',
          'cabinet': '$$this.cabinet'
        }
      }
    }
  }}
 ])

That is what you use to "re-map" the contents of an array, which is really what you are asking. It's usage is much the same as the operator of much the same name in python as well as many other languages.

If you have MongoDB 3.6, you alternately can use a different $lookup syntax where you can actually "choose" which fields to return from there:

db.sample.aggregate([
  {'$lookup': {
    'from': 'locators',
    'let': { 'locator': '$locator' },
    'pipeline': [
      { '$match': {
        '$expr': { '$eq': [ '$_id', '$$locator' ] }
      }},
      { '$project': { 'section': 1, 'cabinet': 1, '_id': 0 } }
    ],
    'as': "metalocator"
  }}
])

That would actually be more efficient where you can do it, since the data is simply not even returned into the target array and you don't need to "re-map" the array in order to discard the other fields.

For the record, what you "missed" was the $first operator:

db.sample.aggregate([
  { "$lookup": {
    "from": "locators",
    "localField": "locator",
    "foreignField": "_id",
    "as": "metalocator"
  }},
  { "$unwind": '$metalocator'},
  { "$group": {
    "_id": "$_id",
    "locator": { "$first": "$locator" },
    "record": { "$first": "$record" },
    "metalocator": {
      "$push":  {
        "section": "$metalocator.section",
        "cabinet": "$metalocator.cabinet"
      }
    }
  }}
])

But using $unwind and $group is not necessary here as the other methods shown above are far more efficient.

Upvotes: 9

Related Questions