CODEWITHSUNDEEP
mongodbaggregation-frameworkpipelinelookup
Alex
Alex

Reputation: 317

Mongodb $lookup nested objects with pipeline

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?

Upvotes: 1

Views: 960

Answers (1)

turivishal
turivishal

Reputation: 36094

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

Upvotes: 1

Related Questions