MongoDB aggregate fill/replace array of ObjectId

Question

I have the following MongoDB structure:

campaign:{
  _id: '5e4eee638552043e60e5073b',
  name: 'Test campaign',
  partners: [
    {
      _id: '5e4fa9fbbdeb4a5878ac2a35',
      options: [Object],
      partner: '5e4e9bc2101ecb2e8764190c',
    },
    {
      _id: '5e4ff7c6e54b6d676d97b7a6',
      options: [Object],
      partner: '5e4ff51c23f44266a8add39e',
    }
  ]
}

Using the following aggregate function, I can lookup the ID of each of the partners and add to a new array:

{
  $lookup:
  {
    from: 'campaigns',
    let: { campaign: '$campaign' },
    pipeline: [
      { $match: { $expr: { $and: [{ $eq: ['$_id', '$$campaign'] }] } } },
      {
        $lookup:
          {
            from: 'partners',
            let: { partner: '$partner.partner' },
            pipeline: [
              { $match: { $expr: { $in: ['$_id', '$$partner'] } } },
              { $project: { _id: 1, name: 1 } }
            ],
            as: 'partner1'
          }
        },
        {
          $project: {
            _id: 1, shortName: 1, userInterface: 1, options: 1, check: 1, description: 1, partner: 1, partner1: 1 } }
    ],
    as: 'campaign'
  }
}

This is new output:

campaign: {
  _id: '5e4eee638552043e60e5073b',
  name: 'Test campaign',
  partners: [
    ...
  ],
  partner1: [
    { _id: '5e4e9bc2101ecb2e8764190c', name: '123' },
    { _id: '5e4ff51c23f44266a8add39e', name: '456' }
  ]
}

But what I want to do is have the _id of each element in partner array be replaced with the full partner object.

Partner object:

partner
{ 
  _id: '5e4e9bc2101ecb2e8764190c',
  name: '123',
  etc: ...
},
{
  _id: '5e4ff51c23f44266a8add39e',
  name: '456'
  etc: ...
}

This is what the final object should look like:

[
  {
    "campaign": [
      {
        "partner": [
          {
            "_id": "5e4eee638552043e60e5073b",
            "options": [
              {}
            ],
            "partner": {
                "_id": "5e4e9bc2101ecb2e8764190c",
                "name": "123"
            }
          }
        ]
      }
    ]
  }
]

Thanks @Valijon, this is a useful tool, but not quite was I was looking for as this replaces the _id and options from original array. I need to keep them and merge in the partner. — Roo
– Roo, Commented Feb 22, 2020 at 13:57

Valijon · Accepted Answer · 2020-02-22 14:49:42Z

1

You need to add extra 3 stages:

db.collection.aggregate([
  //your $lookup,
  {
    $unwind: "$campaign"
  },
  {
    $project: {
      _id: 1,
      "campaign.partner": {
        $map: {
          input: "$campaign.partner",
          as: "partner",
          in: {
            $let: {
              vars: {
                partner1: {
                  $filter: {
                    input: "$campaign.partner1",
                    cond: {
                      $eq: [
                        "$$partner.partner",
                        "$$this._id"
                      ]
                    }
                  }
                }
              },
              in: {
                // we merge partner[i] with partner1[j]
                $mergeObjects: [
                  "$$partner",
                  {
                    partner: {
                      $arrayElemAt: [
                       "$$partner1",
                        0
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      campaign: {
        $push: "$campaign"
      }
    }
  }
])

MongoPlayground

edited Feb 22, 2020 at 14:49

answered Feb 22, 2020 at 14:25

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

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

3 Comments

Roo Over a year ago

Thanks so much, this is 99% there, but the _id is being replaced with the partner _id. "_id": "5e4e9bc2101ecb2e8764190c" in the first array should be "_id": "5e4fa9fbbdeb4a5878ac2a35".

Roo Over a year ago

I've added what the final object should look like above :)

Roo Over a year ago

Brilliant, thanks @Valijon - exactly what I needed! You're a star!

Collectives™ on Stack Overflow

MongoDB aggregate fill/replace array of ObjectId

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related