With MongoDB 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 collection

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 an error:

$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