Output/project with nested array to join

Working with Data
1

Hi,

Let’s say I have these collections

// collection name: Collection1
{_id: 1, name: 'name1'}
{_id: 2, name: 'name2'}
{_id: 3, name: 'name3'}

// collection name: Collection2
{
  _id: 9,
  my_array: [
    {
      value: 5,
      collection1_id: 2,
      collection1_ids: [1]
    },
    {
      value: 7,
      collection1_id: 3,
      collection1_ids: [2,3]
    }
  ]
}

I want to output Collection2 with join to Collection1 via collection1_ids and the output should look like this

{
  _id: 9,
  my_array: [
    {
      value: 5,
      collection1_id: 2,
      collection1_name: 'name2',
      collection1_array: [
        {_id: 1, name: 'name1'}
      ]
    },
    {
      value: 7,
      collection1_id: 3,
      collection1_name: 'name3',
      collection1_array: [
        {_id: 2, name: 'name2'},
        {_id: 3, name: 'name3'}
      ]
    }
  ]
}

I tried to use pipeline aggregation + map + lookup equality match without success.

[
  $lookup: {
    from: 'Collection1',
    localField: 'my_array.collection1_id',
    foreignField: '_id',
    as: 'AsCollection1'
  },
  $lookup: {
    from: 'Collection1',
    localField: 'my_array.collection1_ids',
    foreignField: '_id',
    as: 'AsCollection1s'
  },
  $project: {
    _id: '$_id',
    my_array: {
      $map: {
        input: '$AsCollection1',
        as: 'collection1',
        in: {
          $mergeObjects: [
            {
              collection1_id: '$$collection1._id',
              collection1_name: '$$collection1.name',
            },
            {
              $arrayElemAt: [
                {
                  $map: {
                    input: '$my_array',
                    as: 'my_array',
                    in: {
                      value: '$$my_array.value',
                      collection1_array: {
                        $map: {
                          input: '$AsCollection1s',
                          as: 'collection1s',
                          in: {
                            _id: '$$collection1s._id',
                            name: '$$collection1s.name'
                          }
                        }
                      },
                    }
                  }
                },
                {$indexOfArray: ['$my_array._id', '$$collection1._id']},
              ],
            }
          ]
        }
      }
    }
  }
]

How can I achieve this?

Thank you,

2

Hello @Jean-Luc_K Welcome to MongoDB developer forum,

  • $unwind to deconstruct my_array array
  • $lookup with Collection1
  • $group by _id and reconstruct my_array array
db.Collection2.aggregate([
  { $unwind: "$my_array" },
  {
    $lookup: {
      from: "Collection1",
      localField: "my_array.collection1_ids",
      foreignField: "_id",
      as: "my_array.collection1_ids"
    }
  },
  {
    $group: {
      _id: "$_id",
      my_array: { $push: "$my_array" }
    }
  }
]);
3

Share what you tried and what is wrong with your result and we can more easily indicate what is wrong with your approach.

4

I edited my first post:

  • I forgot there was another property to join to the same Collection1
  • I added what I tried

From what I tried, collection1_array output an aggregation of my_array.collection1_ids when it shouldn’t aggregate and I didn’t find solution for that.

Notes:

  1. I was wondering if it is possible to nested lookup with my_array.collection1_ids directly to build collection1_array then it would be more obvious and my issue would be solved otherwise?
  2. Is there any internal/system variable to know the current array index or current object of array we are working on so we don’t need to use $arrayElemAt with $indexOfArray?

@turivishal
I forgot another property and there was another join.
Also I was wondering if we could output via $project instead.

@steevej
I edited my first to add what I had tried.

5

Yes it is possible, but I would suggest you to use $unwind and $group, to avoid memory limit, you can refer aggregation pipeline Memory Restriction.

db.Collection2.aggregate([
  { $unwind: "$my_array" },
  {
    $lookup: {
      from: "Collection1",
      localField: "my_array.collection1_id",
      foreignField: "_id",
      as: "my_array.collection1_name"
    }
  },
  {
    $lookup: {
      from: "Collection1",
      localField: "my_array.collection1_ids",
      foreignField: "_id",
      as: "my_array.collection1_array"
    }
  },
  {
    $set: {
      "my_array.collection1_name": {
        $arrayElemAt: ["$my_array.collection1_name.name", 0]
      }
    }
  },
  { $unset: "my_array.collection1_ids" },
  {
    $group: {
      _id: "$_id",
      my_array: { $push: "$my_array" }
    }
  }
])