MongoDB aggregation to replace array of ObjectIds with actual documents

Question

Let's say we have this db.orders document collection with one document:

{"_id": "5ef62e0c586c78491530e4d5", "items": [{ "description": "desc1", "parts": ["5ef62e0c586c78491530e4d7", "5ef62e0c586c78491530e4d8"] } ], "createdBy": "userId"}

[CORRECTION: the items field is now an array]

And another db.parts collection with two part documents:

{"_id": "5ef62e0c586c78491530e4d7", "name": "part1" }
{"_id": "5ef62e0c586c78491530e4d8", "name": "part2" }

I would like to construct an aggregation on the first collection replacing the ObjectIds in the parts array with the actual documents. The result should be like:

{"_id": "5ef62e0c586c78491530e4d5", "items": [{ "description": "desc1", "parts":
  [
    {"_id": "5ef62e0c586c78491530e4d7", "name": "part1" },
    {"_id": "5ef62e0c586c78491530e4d8", "name": "part2" }
  ]
}], "createdBy": "userId"}

Any ideas would be much appreciated!

Valijon · Accepted Answer · 2020-06-27 09:33:30Z

1

You need to use the $lookup operator:

db.orders.aggregate([
  {
    $lookup: {
      from: "parts",
      localField: "items.parts",
      foreignField: "_id",
      as: "parts"
    }
  },
  {
    $project: {
      createdBy: 1,
      items: {
        $map: {
          input: "$items",
          as: "item",
          in: {
            description: "$$item.description",
            parts: {
              $filter: {
                input: "$parts",
                as: "part",
                cond: {
                  $in: [ "$$part._id", "$$item.parts" ]
                }
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground

edited Jun 27, 2020 at 9:33

answered Jun 27, 2020 at 7:37

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

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

2 Comments

Thomas Skyttegaard Hansen Over a year ago

Thanks! I actually made a mistake in my question. The "items" field should be an array like: {"_id": "5ef62e0c586c78491530e4d5", "items": [{ "description": "desc1", "parts": ["5ef62e0c586c78491530e4d7", 5ef62e0c586c78491530e4d8] }], "createdBy": "userId"} Doing the lookup it does not preserve the items as an array and I loose the "description" field.

Valijon Over a year ago

@ThomasSkyttegaardHansen check again please, I've updated my answer

Collectives™ on Stack Overflow

MongoDB aggregation to replace array of ObjectIds with actual documents

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related