With mongo aggregate, how do you lookup child documents from an array on the parent document and match a field on the child to a value?

I want to lookup the ShopItems for a Shop. And I only want the ShopItems that are not kidFriendly.

Details:

Let's say I have Shops

Shop Schema

{
shopItems: [   
      {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'ShopItem',
      },
    ],
}

And a shop can have multiple Shop Items:

ShopItem Schema

[
itemName:  { type: String },
kidFriendly:  { type: Boolean, default: false },
]

I want to lookup the ShopItems for a Shop. And I only want the ShopItems that are not kidFriendly.

I tried

      const result3 = await Shop.aggregate([
        {
          $match: {
            _id: mongoose.Types.ObjectId('623ae52ba5b1af0004e1c4ec'),
          },
        },
        {
          $lookup: {
            from: ShopItem.collection.collectionName,
            pipeline: [
              {
                $match: {
                  kidFriendly: { $ne: true },
                  _id: { $in: '$shopItems' },
                },
              },
            ],
            as: 'adultOnlyItems',
          },
        },
      ]);

but I get $in needs an array.

example data

// shops
[
{
_id: ObjectId('623ae52ba5b1af0004e1c4ec'),
shopItems: [ObjectId('631e6b133b688a0004a17265'), ObjectId('62f4cc974a255f00044c01b5'), ObjectId('625ffc48eec7b20004c9294c')]
},
{
_id: ObjectId('623ae52ba5b1af0004e1c4eb'),
shopItems: [ObjectId('631e6b133b688a0004a17263')]
}
]

//shop items
[
{
_id: ObjectId('631e6b133b688a0004a17265'),
itemName: "Barbie",
kidFriendly: true
},
{
_id: ObjectId('62f4cc974a255f00044c01b5'),
itemName: "Alcohol",
kidFriendly: false
},
{
_id: ObjectId('625ffc48eec7b20004c9294c'),
itemName: "Glass Vase",
kidFriendly: false
},
{
_id: ObjectId('631e6b133b688a0004a17263'),
itemName: "Beach Ball",
kidFriendly: true
}

]

CodePudding user response：

When you use $lookup with pipeline, you need to have the let to store the field value from shops into variable. And use the $expr operator as well.

db.shops.aggregate([
  {
    $match: {
      _id: mongoose.Types.ObjectId('623ae52ba5b1af0004e1c4ec')
    }
  },
  {
    $lookup: {
      from: ShopItem.collection.collectionName,
      let: {
        shopItems: "$shopItems"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $ne: [
                    "$kidFriendly",
                    true
                  ]
                },
                {
                  $in: [
                    "$_id",
                    "$$shopItems"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "adultOnlyItems"
    }
  }
])

Demo @ Mongo Playground

Reference

Join Conditions and Subqueries on a Joined Collection