MongoDB: make $lookup put its output in objects inside an array

user_1649006 0
Viewed 51

Problem

Say I have this document in a project-storage collection:

{
  name: 'storage',
  containers: [
    { name: 'container1', items: ['item1', 'item2'] },
    { name: 'container2', items: ['item2', 'item3'] },
  ],
}

And these in a project-items collection:

{ name: 'item1', value: 1 },
{ name: 'item2', value: 1 },
{ name: 'item3', value: 1 },

I want to write a query that reads the storage document and inlines the items of each container inside the container, to have this:

{
  name: 'storage',
  containers: [
    {
      name: 'container1',
      items: ['item1', 'item2'],
      _inlinedItems: [{ name: 'item1', value: 1 }, { name: 'item2', value: 1 }]
    },
    {
      name: 'container2',
      items: ['item2', 'item3'],
      _inlinedItems: [{ name: 'item2', value: 1 }, { name: 'item3', value: 1 }]
    },
  ],
}

Failed attempt

Using an aggregate and the $lookup operator, I tried this:

[
  { $match: { name: 'storage' } },
  {
    $lookup: {
      from: 'project-items',
      localField: 'containers.items',
      foreignField: 'name',
      as: 'containers._inlinedItems',
    },
  },
]

Which does not work, because 1) it erases the container field completely and 2) it mixes the items from the two containers:

{
  name: 'storage',
  containers: {
    _inlinedItems: [{ name: 'item1', value: 1 }, { name: 'item2', value: 1 }, { name: 'item3', value: 1 }]
  }
}

Is there a way to do this for example by putting something special in the as field of the $lookup operator?

Unsatisfactory solution

The best I could do is to unwind the containers, do the lookup and then group the documents back together:

[
  { $match: { name: 'storage' } },
  { $unwind: '$containers' },
  {
    $lookup: {
      from: 'project-items',
      localField: 'containers.items',
      foreignField: 'name',
      as: 'containers._inlinedItems',
    },
  },
  {
    $group: {
      _id: '$_id' ,
      name: { $first: '$name' },
      containers: { $push: '$containers' },
    },
  },
]

Which works, but I have to list all the properties from the storage document which I want to keep (for example name here), which is very problematic in my case since I do not know the names of all the properties, but I do need to keep them.

Proper solution?

Am I missing something obvious? Is there a way to specify to the $lookup operator that I want it to write inside the container element it read the items from? Or is the unwind + group the only way to do this?

mongodb
1 Answers

You can try without $unwind, I have not compared performance of both but you can check it, if this will help you,

  • $match your condition
  • $lookup with items and get in items array
  • $project to show required fields
  • $map on containers iterate loop and doo filter on items using $filter and check condition if match items then return in _inlinedItems and then merge objects with current index object using $mergeObjects
  { $match: { name: "storage" } },
  {
    $lookup: {
      from: "project-items",
      localField: "containers.items",
      foreignField: "name",
      as: "items"
    }
  },
  {
    $set: {
      containers: {
        $map: {
          input: "$containers",
          as: "c",
          in: {
            $mergeObjects: [
              "$$c",
              {
                _inlinedItems: {
                  $filter: {
                    input: "$items",
                    cond: { $in: ["$$this.name", "$$c.items"] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { $project: { items: 0 } }

Playground

user_89871280
Related