MongoDB Aggregate $lookup on array of object with additional field

Question

I have two different collections (example below) methods & items. As for now, I'm using pre 3.6 vanilla aggregation query for $lookup:

MongoPlayground Example

  {
    $lookup: {
      from: "items",
      localField: "reagents._id",
      foreignField: "_id",
      as: "reagent_items"
    }
  }

The problem is that if I am using it, I miss quantity field (from methods.reagents embedded) during $lookup stage from original collection. For now, I return quantity right after lookup but as I heard, Mongo introduced from 3.6 a new syntax for lookup queries, so the question is:

Can it solve my problem for receiving the following results:

  {
    "_id": 1,
    "name": "Test",
    "reagent_items": [ // <= the exact schema of what I need after lookup
      {
        "_id": 1,
        "name": "ItemOne",
        "other": "field",
        "quantity": 2 //quantity field from original {array of objects} after lookup
      },
      {
        "_id": 2,
        "name": "ItemTwo",
        "other": "field",
        "quantity": 4 //quantity field from original {array of objects} after lookup
      }
    ],
    "reagents": [ //original reagents field here just for example, we could remove it
      {
        "_id": 1,
        "quantity": 2
      },
      {
        "_id": 2,
        "quantity": 4
      }
    ]
  }

methods

    {
      "_id": 1,
      "name": "Test",
      "reagents": [
        {
          _id: 1,
          quantity: 2
        },
        {
          _id: 2,
          quantity: 4
        }
      ]
    }

items

    {
      "_id": 1,
      "name": "ItemOne",
      "other": "field"
    },
    {
      "_id": 2,
      "name": "ItemTwo",
      "other": "field"
    }

mickl · Accepted Answer · 2020-04-30 01:28:21Z

3

Use $map along with $arrayElemAt to find corresponding reagent for each reagent_items and the apply $mergeObjects to get one object:

db.methods.aggregate([
    {
        $lookup: {
            from: "items",
            localField: "reagents._id",
            foreignField: "_id",
            as: "reagent_items"
        }
    },
    {
        $project: {
            _id:1,
            name: 1,
            reagents: 1,
            reagent_items: {
                $map: {
                    input: "$reagent_items",
                    as: "ri",
                    in: {
                        $mergeObjects: [
                            "$$ri",
                            {
                                $arrayElemAt: [ { $filter: { input: "$reagents", cond: { $eq: [ "$$this._id", "$$ri._id" ] } } }, 0 ]
                            }
                        ]
                    }
                }
            }
        }
    }
])

Mongo Playground

answered Apr 30, 2020 at 1:28

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

4 Comments

AlexZeDim Over a year ago

Well, I have faced a problem with almost a relevant query: If I have index on _id field in collection that should been lookuped and the reagents array have item_id reversal order, for example not [1,2,3] but [3,2,1], well quantity` field can lose the correct item id after $map stage.

mickl Over a year ago

@AlexZeDim not sure if I understand your concern, there's a { $eq: [ "$$this._id", "$$ri._id" ] } condition so order won't affect matching

AlexZeDim Over a year ago

yes, I haven't seen it at first, as for now, I am rechecking this thing. Well as for my problem it's hard to describe with only 500 symbols in comment, but I ruined my first DB because of wrong schema pattern.

byal Over a year ago

Can you please answer this question? stackoverflow.com/questions/64661069/… @mickl

Collectives™ on Stack Overflow

MongoDB Aggregate $lookup on array of object with additional field

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related