4

Let's say we have 3 hypothetical collections in MongoDB: customers, orders, and orderItems.

Each customer has multiple orders, and each order has multiple order items.

Here's some sample data for these 3 collections:

customers

[
    {
        customer_id: 1,
        name: "Jim Smith",
        email: "jim.smith@example.com"
    },
    {
        customer_id: 2,
        name: "Bob Jones",
        email: "bob.jones@example.com"
    }
]

orders

[
    {
        order_id: 1,
        customer_id: 1
    },
    {
        order_id: 2,
        customer_id: 1
    }
]

orderItems

[
    {
        order_item_id: 1,
        name: "Foo",
        price: 4.99,
        order_id: 1
    },
    {
        order_item_id: 2,
        name: "Bar",
        price: 17.99,
        order_id: 1
    },
    {
        order_item_id: 3,
        name: "baz",
        price: 24.99,
        order_id: 2
    }
]

Desired Result

How can I write my aggregation pipeline so that the result returned looks something like this?

[
    {
        customer_id: 1,
        name: "Jim Smith",
        email: "jim.smith@example.com"
        orders: [
            {
                order_id: 1,
                items: [
                    {
                        name: "Foo",
                        price: 4.99
                    },
                    {
                        name: "Bar",
                        price: 17.99
                    }
                ]
            },
            {
                order_id: 2,
                items: [
                    {
                        name: "baz",
                        price: 24.99
                    }
                ]
            }
        ]
    },
    {
        customer_id: 2,
        name: "Bob Jones",
        email: "bob.jones@example.com"
        orders: []
    }
]
Greg Thomas
  • 397
  • 3
  • 13

1 Answers1

4

Do nested lookup using lookup with pipeline,

  • $lookup with orders collection,
    • let, define variable customer_id that is from main collection, to access this reference variable inside pipeline using $$ like $$customer_id,
    • pipeline can add pipeline stages same as we do in root level pipeline
    • $expr whenever we match internal fields it requires expression match condition, so $$customer_id is parent collection field that declared in let and $customer_id is child collection's/current collection's field
  • $lookup with orderitems collection
db.customers.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { customer_id: "$customer_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$customer_id", "$customer_id"] } } },
        {
          $lookup: {
            from: "orderitems",
            localField: "order_id",
            foreignField: "order_id",
            as: "items"
          }
        }
      ],
      as: "orders"
    }
  }
])

Playground

Tip:

Several joins considered as bad practice in NoSQL, I would suggest if you could add your order items in orders collection as array, you can save one join process for orderitems, see improved version in playground

turivishal
  • 34,368
  • 7
  • 36
  • 59
  • Can you explain how the `$match` stage inside of the `$lookup` pipeline works and why it's necessary? In this example, it looks like if you remove the `$match` stage entirely, the results are still the same. – Greg Thomas Feb 03 '21 at 18:43
  • 1
    i have updated the details further you will more help from attached $lookup reference link, yes $match is necessary for joining reference documents on the basis of specific field. *it looks like if you remove the $match stage entirely, the results are still the same* it is same because there are no more other documents in collection, add some dummy documents that are not reference of parent document. – turivishal Feb 03 '21 at 18:49
  • Thanks, I accepted your answer. One last thing, kind of unrelated: Is it generally considered bad practice to normalize your Mongo data in such a way that it may require several joins like this? The example I describe here seems totally normal for SQL, but I'm not sure if there's a better way to do things in Mongo. – Greg Thomas Feb 03 '21 at 18:55
  • i have added a tip in answer. – turivishal Feb 03 '21 at 19:09