CODEWITHSUNDEEP
mongodbaggregation-framework
Lucien Chardon
Lucien Chardon

Reputation: 301

How to use $lookup with a nested array as localField

I have a two collections like this:

Collection Countries:

[
    {
        "country": "UK",
        "shops": [
            {"city": "London", "fruits": [1, 2]},
            {"city": "Birmingham", "fruits": [2]},
        ],
    },
    {
        "country": "DE",
        "shops": [
            {"city": "Munich", "fruits": [2, 3]},
            {"city": "Berlin", "fruits": [1, 2, 3]},
        ],
    },
]

Collection Fruits:

 [
    {
        "uid": 1,
        "name": "banana",
    },
    {
        "uid": 2,
        "name": "kiwi",
    },
    {
        "uid": 3,
        "name": "mango",
    },
]

GOAL:

Using the mongo aggregation framework, i want to replace in the $project stage the list of uids at shops.fruits in the the collection "Countries" with the actual documents from the collection fruits. The uid in the documents of the collection Fruits is a custom field and separate from the mongo _id field.

WHAT I TRIED:

I tried the following pipeline

pipeline = [
  {
    $match: {},
  },
  {
    $lookup: {
      from: "fruits",
      localField: "shops.fruits",
      foreignField: "uid",
      as: "shops.fruits",
    },
  },
];

ISSUE:

Unfortunately, this returns for each document in Countries only the first shop with the documents from Fruits, but not for the whole array of shops.

Actual outcome:

[
    {
        "country": "UK",
        "shops": {
            "city": "London",
            "fruits": [
                {
                    "uid": 1,
                    "name": "banana",
                },
                {
                    "uid": 2,
                    "name": "kiwi",
                },
            ],
        },
    },
    // ...
]

DESIRED OUTCOME:

[
    {
        "country": "UK",
        "shops": [
            {
                "city": "London",
                "fruits": [
                    {
                        "uid": 1,
                        "name": "banana",
                    },
                    {
                        "uid": 2,
                        "name": "kiwi",
                    },
                ],
            },
            {
                "city": "Birmingham",
                "fruits": [
                    {
                        "uid": 2,
                        "name": "kiwi",
                    },
                ],
            },
        ],
    },
    // ...
    
]

QUESTION:

How can i use $lookup with a nested array as localField (without an $unwind stage if possible)?

Thanks for your help!

Upvotes: 0

Views: 172

Answers (1)

rickhg12hs
rickhg12hs

Reputation: 11942

You could "populate" the "fruits" array in countries collection documents with the info from the fruits collection without using "$unwind"/"$group", but it's less straightforward. Here's one way you could do it.

N.B.: This aggregation pipeline does not consider the possibility that a "fruits" uid does not exist in the fruits collection. It is also possible that the output "fruits" array will be reordered.

db.countries.aggregate([
  { // get all the fruits for this doc
    "$lookup": {
      "from": "fruits",
      "localField": "shops.fruits",
      "foreignField": "uid",
      "as": "theFruits",
      "pipeline": [
        { // don't want _id
          "$project": {
            "_id": 0
          }
        }
      ]
    }
  },
  {
    "$set": {
      // rewrite shops array
      "shops": {
        // transform each element of shops
        "$map": {
          "input": "$shops",
          "as": "shop",
          "in": {
            // keep everything in object ...
            "$mergeObjects": [
              "$$shop",
              // ... and rewrite fruits array
              {
                "fruits": {
                  // keep all fruits that match for this city
                  "$filter": {
                    "input": "$theFruits",
                    "as": "theFruit",
                    "cond": {
                      "$in": ["$$theFruit.uid", "$$shop.fruits"]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { // don't want/need this anymore
    "$unset": "theFruits"
  }
])

Try it on mongoplayground.net.

Upvotes: 0

Related Questions