papawheelie
papawheelie

Reputation: 64

MongoDB - Aggregate with nested array not working

I have 2 collections using a simple nested array. An example of the records looks like so:

menus

{ 
    "_id" : ObjectId("620323fe80ec16abea7a0205"), 
    "name" : "A new menu", 
    "description" : "", 
    "menuData" : [
        {
            "catName" : "Cat 2", 
            "items" : [
                "61ded42461b1d8966c5efc45", 
                "61ded55001e3cafb8db19198", 
                "61df9adf2441b6113033f341", 
                "61dfa8c82441b6113033f738"
            ]
        }, 
        {
            "catName" : "Cat 1", 
            "items" : [
                "62020691adda4aab89f1742d", 
                "61dfa8c82441b6113033f738"
            ]
        }
    ], 
    "status" : "active", 
}

and I'm trying to complete a lookup on the menu items array (menuData.items). These are object IDs from another collection.

menuitems One example is:

{ 
    "_id" : ObjectId("61ded55001e3cafb8db19198"), 
    "name" : "Coca-Cola 600ml", 
    "description" : "Refreshing taste...ahhh", 
    "price" : 3.95, 
    "tags" : [
        "drinks"
    ], 
    "options" : [

    ], 
    "status" : "active",  
}

Using the following aggregate, I do not get the output of the lookup

db.getCollection("menus").aggregate(
[
    { 
        "$match" : { 
            "_id" : ObjectId("620323fe80ec16abea7a0205")
        }
    }, 
    { 
        "$addFields" : { 
            "newField" : "$menuData.items"
        }
    }, 
    { 
        "$unwind" : { 
            "path" : "$newField"
        }
    }, 
    { 
        "$lookup" : { 
            "from" : "menuitems", 
            "localField" : "newField", 
            "foreignField" : "_id", 
            "as" : "items"
        }
    }
]
);

Output

{ 
    "_id" : ObjectId("620323fe80ec16abea7a0205"), 
    "name" : "A new menu", 
    "description" : "", 
    "menuData" : [
        {
            "catName" : "Cat 2", 
            "items" : [
                "61ded42461b1d8966c5efc45", 
                "61ded55001e3cafb8db19198", 
                "61df9adf2441b6113033f341", 
                "61dfa8c82441b6113033f738"
            ]
        }, 
        {
            "catName" : "Cat 1", 
            "items" : [
                "62020691adda4aab89f1742d", 
                "61dfa8c82441b6113033f738"
            ]
        }
    ], 
    "status" : "active", 
    "createdBy" : ObjectId("61bb07c778e39ca45c161d81"), 
    "createdByModel" : "Administrator", 
    "createdAt" : ISODate("2022-02-09T02:16:30.108+0000"), 
    "updatedAt" : ISODate("2022-02-09T04:26:00.837+0000"), 
    "__v" : NumberInt(0), 
    "newField" : [
        "61ded42461b1d8966c5efc45", 
        "61ded55001e3cafb8db19198", 
        "61df9adf2441b6113033f341", 
        "61dfa8c82441b6113033f738"
    ], 
    "items" : [

    ]
}

As you can see, items are empty. I tried to complete this without $addFields and $unwind - with the same result.

Any help is greatly appreciated.

Upvotes: 0

Views: 136

Answers (1)

Yong Shun
Yong Shun

Reputation: 51440

You need $lookup with pipeline.

  1. Converting ObjectId to string (newFields hold set of Id strings while menuItems document Id is an ObjectId)
  2. Check the converted id string is within the newFields array via $in.
{
  "$lookup": {
    "from": "menuitems",
    let: {
      newField: "$newField"
    },
    pipeline: [
      {
        $match: {
          $expr: {
            $in: [
              {
                $toString: "$_id"
              },
              "$$newField"
            ]
          }
        }
      }
    ],
    "as": "items"
  }
}

Sample Demo on Mongo Playground

Upvotes: 1

Related Questions