Mongodb $lookup nested objects with pipeline

Question

I have the following schema Thing:

{ name: "My thing", files: [ { name: "My file 1", versions: [ { file_id: ObjectId("blahblahblah") }, { file_id: ObjectId("blahblahblah") }, ], }, { name: "My file 2", versions: [ { file_id: ObjectId("blahblahblah") }, { file_id: ObjectId("blahblahblah") }, ], } ] }

And then I a have a File schema:

{ _id: ObjectId("blahblah"), type: "image", size: 1234, }

The file_id in the Thing schema is a REF to the _id of the File schema.

I want to $lookup all the files inside my Thing. So I started with this:

{ "$lookup": { "from": "files", "let": { "files": "$files" }, "pipeline": [ { "$match": { "$expr": { "$in": [ "$_id", "$$files.versions.file_id" ] } } }. ], "as": "files.versions.file" } }

But it's obviously wrong. Can someone help?

turivishal · Accepted Answer · 2021-02-06 16:27:14Z

The problem is when we $$files.versions.file_id access ids it will return array of array of ids so $in will not match nested array of ids,

I can see you are trying to project file details in same nested level, so direct lookup will not set that detail in nested array, you have to deconstruct the array first before set files details,

$unwind deconstruct files array
$unwind deconstruct versions array
$lookup with files collection and pass files.versions.file_id as localField
$unwind deconstruct files.versions.file_id array
$group by name and file name and re-construct versions array
$group by name only and reconstruct files array

 { $unwind: "$files" }, { $unwind: "$files.versions" }, { $lookup: { from: "files", localField: "files.versions.file_id", foreignField: "_id", as: "files.versions.file_id" } }, { $unwind: "$files.versions.file_id" }, { $group: { _id: { name: "$name", file_name: "$files.name" }, versions: { $push: "$files.versions" } } }, { $group: { _id: "$_id.name", files: { $push: { name: "$_id.file_name", versions: "$versions" } } } }

Playground

Collectives™ on Stack Overflow

Mongodb $lookup nested objects with pipeline

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related