MongoDB $lookup on doubly nested array of objects

Question

I have a data model where each product has many variants and each variant has many modifications. In the database, it looks like this:

const mods = db.modifications.insertMany([
  {
    title: 'Modification #1',
    image: 'img1.png',
  },
  {
    title: 'Modification #2',
    image: 'img2.png',
  },
  {
    title: 'Modification #3',
    image: 'img3.png',
  },
])

db.products.insertOne({
  slug: 'product1',
  title: 'Product #1',
  variants: [
    {
      size: 20,
      price: 200,
      modifications: [
        { id: mods.insertedIds[0], price: 10 },
        { id: mods.insertedIds[1], price: 15 },
      ],
    },
    {
      size: 30,
      price: 250,
      modifications: [
        { id: mods.insertedIds[0], price: 15 },
        { id: mods.insertedIds[2], price: 20 },
      ],
    },
  ],
})

https://mongoplayground.net/p/6jmEv2Q2aZO

What I want is to do

db.products.aggregate([
  { $match: { slug: 'product1' } },
  // ?
])

To get the result that looks like this

{
  slug: 'product1',
  title: 'Product #1',
  variants: [
    {
      size: 20,
      price: 200,
      modifications: [
        { _id: '…', title: 'Modification #1', image: '…', price: 10 },
        { _id: '…', title: 'Modification #2', image: '…', price: 15 },
      ],
    },
    {
      size: 30,
      price: 250,
      modifications: [
        { _id: '…', title: 'Modification #2', image: '…', price: 15 },
        { _id: '…', title: 'Modification #3', image: '…', price: 20 },
      ],
    },
  ],
}

How to accomplish it in MongoDB?

I've tried to $unwind twice and then $lookup

db.products.aggregate([
  { $match: { slug: 'product1' } },
  { $unwind: '$variants' },
  { $unwind: '$variants.modifications' },
  {
    $lookup: {
      from: 'modifications',
      localField: 'variants.modifications.id',
      foreignField: '_id',
      let: { price: '$variants.modifications.price' },
      pipeline: [{ $addFields: { price: '$$price' } }],
      as: 'modifications',
    },
  },
])

but don't know how to $group (?) that data back.

Also, there's a similar question with the working solution. In my case though, the modifications array isn't just an array of ids, but has data within its elements (the price field) which I need to include in the result somehow.

Answer 1

You can achieve without the $unwind stage(s).

1. $match

2. $lookup - Fetch matched document from the modifications collection with _id in the flattened modIds (variants.modifications.id) and output as modifications array.

3. $set - Set the variants field.

   3.1. $map - Iterate the variants array by merging the current v (variant) object and the object from the result 3.1.1.

   3.1.1. $map - The object contains the modifications array by iterating the modifications array, merging the current m (modification) object and the object from the result 3.1.1.1.

   3.1.1.1. $first - Get the first matching element from the result 3.1.1.1.1.

   3.1.1.1.1. $filter - Filter the matching document from the (root) modifications array with m.id.

4. $unset - Remove "modifications" and "variants.modifications.id" fields.

db.products.aggregate([
  {
    "$match": {
      slug: "product1"
    }
  },
  {
    "$lookup": {
      "from": "modifications",
      "let": {
        modIds: {
          $reduce: {
            input: "$variants.modifications.id",
            initialValue: [],
            in: {
              $concatArrays: [
                "$$value",
                "$$this"
              ]
            }
          }
        }
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              $in: [
                "$_id",
                "$$modIds"
              ]
            }
          }
        }
      ],
      "as": "modifications"
    }
  },
  {
    $set: {
      variants: {
        $map: {
          input: "$variants",
          as: "v",
          in: {
            $mergeObjects: [
              "$$v",
              {
                modifications: {
                  $map: {
                    input: "$$v.modifications",
                    as: "m",
                    in: {
                      $mergeObjects: [
                        "$$m",
                        {
                          $first: {
                            $filter: {
                              input: "$modifications",
                              cond: {
                                $eq: [
                                  "$$this._id",
                                  "$$m.id"
                                ]
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [
      "modifications",
      "variants.modifications.id"
    ]
  }
])

Demo @ Mongo Playground