Join on doubly nested array of objects

Working with Data Drivers
, ,
1

I have a data model where each Product has many Variants and each Variant has many Modifications. In 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 },
      ],
    },
  ],
})

What I want is to do

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

to get the result that looks like this

const result = {
  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 this?

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: 'variants.modifications',
    },
  },
])

but then I don’t know how to $group (?) that data back.

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

3

Hi @crabvk and welcome to MongoDB community forums!!

Thank you for sharing the detailed information on the posts.
Based on my understanding

Firstly, using the combination of two unwinds and group together may result in a suboptimal query and if this is a frequently used query in the application, might impact on the performance of the application.
The initial recommendation would be to re-design the schema to make it easier to achieve the result desired. Depending on your use case, an example would be to reduce the nested arrays.
If you have the related schema, the documentations for Best Practices for Data Modelling would be a good staring point for reference.

Now based on your use case, I tried to replicate the query in my local environment and tried the following query. The result is not exactly as desired, but this is quite close and may be suitable for your use case.

I tried the following query as:


Atlas atlas-cihc7e-shard-0 [primary] test> db.products.aggregate([ { $match: { slug: "product1" } }, { $unwind: { path: "$variants" } }, { $unwind: { path: "$variants.modifications" } }, { $lookup: { from: "modifications", localField: "variants.modifications.id", foreignField: "_id", let: { price: "$variants.modifications.price" }, pipeline: [ { $addFields: { price: "$$price" } }], as: "variants.modifications" } }, { $group: { _id: { slug: "$slug", size: "$variants.size" }, title: { $first: "$title" }, totalPrice: { $sum: "$variants.price" }, modifications: { $addToSet: "$variants.modifications" } } }, { $project: { _id: 0, slug: "$_id.slug", size: "$_id.size", title: 1, totalPrice: 1, modifications: 1 } }] )
[
  {
    title: 'Product #1',
    totalPrice: 400,
    modifications: [
      [
        {
          _id: ObjectId("6531197917a38d454218eede"),
          title: 'Modification #1',
          image: 'img1.png',
          price: 10
        }
      ],
      [
        {
          _id: ObjectId("6531197917a38d454218eedf"),
          title: 'Modification #2',
          image: 'img2.png',
          price: 15
        }
      ]
    ],
    slug: 'product1',
    size: 20
  },
  {
    title: 'Product #1',
    totalPrice: 500,
    modifications: [
      [
        {
          _id: ObjectId("6531197917a38d454218eee0"),
          title: 'Modification #3',
          image: 'img3.png',
          price: 20
        }
      ],
      [
        {
          _id: ObjectId("6531197917a38d454218eede"),
          title: 'Modification #1',
          image: 'img1.png',
          price: 15
        }
      ]
    ],
    slug: 'product1',
    size: 30
  }
]

Does the above output helpful to you ?

Warm Regards
Aasawari

4

Thank you for the query and the link, definitely must read topic.
Regarding my initial question, I’ve got the solution on stackoverflow join - MongoDB $lookup on doubly nested array of objects - Stack Overflow

1 Like