MongoDB aggregation query feasibility

Question

I am evaluating MongoDB for an application and I am trying to learn how to use it.

I don't know whether what I want to achieve is possible, so I am prepared for "No" as an answer.

Suppose the three collections described below (in mongoplayground-style format)

db={
  packages: [
    {
      _id: 10,
      name: "small box"
    },
    {
      _id: 20,
      name: "big box"
    }
  ],
  shipments: [
    {
      customer: "bob",
      items: [
        {
          _id: 12312,
          package_id: 20,
          weight: 9.99,
          
        },
        {
          _id: 65489,
          package_id: 10,
          weight: 1.5
        }
      ]
    }
  ]
}

I want to use the aggregation framework to produce a document a shipment, where for each item a new property is added that contains the information regarding the package (example given below)

{
      customer: "bob",
      items: [
        {
          _id: 12312,
          package_id: 20,
          weight: 9.99,
          package: {
            _id: 20,
            name: "big box"
          }
        },
        {
          _id: 65489,
          package_id: 10,
          weight: 1.5,
          package: {
            _id: 10,
            name: "small box"
          }
        }
      ]
    }

I have tried using $lookup without a pipeline, but I can only get up to a point where I replace each item document with the corresponding package document (which of course is not what I want to achieve), and I am lost with using $lookup with a pipeline (which if I were a betting man, I'd bet is the way to achieve what I want). I kind of got somewhere by $unwind'ing the items array, followed by a $lookup and a $group / $push but then I am not sure how to retrieve the other fields of a shipment document other than the items (and I have a feeling this is not the proper way to achieve the desired result).

I can post more code if needed (what I have tried so far), but I am trying to keep the question within a reasonable length

Any help would be appreciated, as I am sure I could be trying for days to produce the sample I want and I am not even sure it is possible.

Usually in NoSQL databases you try to avoid JOINs (or lookups). Better put them into one collection from the beginning. Don't think in relational design you may be used from RDBMS — Wernfried Domscheit
– Wernfried Domscheit, Commented Mar 6, 2021 at 16:18
I am trying to ultimately save space by not repeating the same fields/properties every time, but I do understand that this may come from previous relational db background. This isn't a problem for a case like this, but when dozens of fields will get repeated for every document, maybe it will sum up to some meaningful number. Thanks for the tip, nevertheless ! — Konstantinos
– Konstantinos, Commented Mar 6, 2021 at 16:47
Where is the 3rd collection out of 3 collections that u mentioned in the question? — Dheemanth Bhat
– Dheemanth Bhat, Commented Mar 6, 2021 at 17:37

RoMerr · Accepted Answer · 2021-03-07 00:09:57Z

1

db.shipments.aggregate([
      {
        $lookup: {
          from: "packages",
          localField: "items.package_id",
          foreignField: "_id",
          as: "packages"
        }
      },
      {
        $project: {
          customer: 1,
          items: {
            $map: {
              input: "$items",
              as: "item",
              in: {
                "$mergeObjects": [
                  {
                    "_id": "$$item._id",
                    "package_id": "$$item.package_id",
                    "weight": "$$item.weight"
                  },
                  {
                    "package": {
                      "$arrayElemAt": [
                        {
                          "$filter": {
                            "input": "$packages",
                            "as": "package",
                            "cond": {
                              "$eq": [
                                "$$item.package_id",
                                "$$package._id"
                              ]
                            }
                          }
                        },
                        0
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      }
    ])

mongoplayground

answered Mar 7, 2021 at 0:09

RoMerr

262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Konstantinos Over a year ago

Wow... I don't know who you are, but I will find you and thank you... My last resort was to use the $lookup stage just like you did and then iterate over and map all package_ids to a new object in code. I never thought to check whether the db can do it for me. Thanks again !

Dheemanth Bhat · Accepted Answer · 2021-03-06 17:39:38Z

0

Try this:

db.shipments.aggregate([
    { $unwind: "$items" },
    {
        $lookup: {
            from: "packages",
            let: { package_id: "$items.package_id" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: ["$_id", "$$package_id"]
                        }
                    }
                }
            ],
            as: "items.package"
        }
    },
    { $unwind: "$items.package" },
    {
        $group: {
            _id: "$_id",
            customer: { $first: "$customer" },
            items: { $push: "$items" }
        }
    }
])

Solution in Playground

edited Mar 6, 2021 at 17:39

answered Mar 6, 2021 at 17:27

Dheemanth Bhat

4,5022 gold badges30 silver badges43 bronze badges

1 Comment

Konstantinos Over a year ago

Thank you for your answer, but the fact where i have to use ``` customer: { $first: "$customer" } ``` is painful. I don't want to have to do this for every field in the document, and that is why I never believed that $unwinding and $grouping was the way to go

Collectives™ on Stack Overflow

MongoDB aggregation query feasibility

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related