Populate array of ObjectIds nested inside an object preserving document shape

Working with Data
, ,
1

Hi! I have a collection which is structured in the following way:

{
  nested: {
    users: [
      { user: ObjectId('...'), status: 'pending' },
      { user: ObjectId('...'), status: 'approved' },
    ]
  }
}

I wish to use an aggregation pipeline to populate the “users” field so the document gets turned into the following:

{
  nested: {
    users: [
      { user: { _id: ObjectId('...'), name: 'John' }, status: 'pending' },
      { user: { _id: ObjectId('...'), name: 'Mike' }, status: 'approved' },
    ]
  }
}

The problem is:

  • I need to unwind “users” first in order to perform the lookup, so “users” will get turned into a single object
  • I cannot rebuild the “users” array with the “$group” stage because “$group” doesn’t support nested fields

Some details to be considered:

  • I’m using MongoDB NodeJS driver only – without tools like Mongoose
  • It is important for my use case to use a single aggregation, without further JS processing
  • Document must be retrieved on it’s original shape
  • Reshaping the data is not an option, the data was just an example of a more general problem I’m experiencing

The pipeline I’ve got so far (doesn’t work because the “users” array gets flattened into a single document):

[
  {
    "$match": {
      "_id":ObjectId( "66b93fc2007e4988489dfa0b")
    }
  },
  {
    "$unwind": {
      "path": "$nested",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$unwind": {
      "path": "$nested.users",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$lookup": {
      "from": "users",
      "let": {
        "ids": "$nested.users.user"
      },
      "as": "nested.users.user",
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$eq": [
                "$_id",
                "$$ids"
              ]
            }
          }
        }
      ]
    }
  },
  {
    "$unwind": {
      "path": "$nested.users.user",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "nested": {
        "$first": "$nested"
      }
    }
  }
]
2

You do not need to $unwind for $lookup. It is smart enough to $lookup each element. See this playground for an example.

The $group stage does not support nested fields. But you may create a non-nested one with group and then use $set to create the structure you want.

But you should never $unwind and array just to group it later. The array operations are sufficient most of the time enough. Unwind is expansive in terms of memory and group is blocking until all input documents are consumed.

In your case, after the origin $lookup you may use $map on nested.users with $mergeObjects to optain what you wish. $indexOfArray could be used to locate the object in _tmp.lookup array.

A final $merge stage is then need to replace nested.users with the mapped version.

3

Thank you very much for your time. I managed to get it working following your tips.

Here’s the version without $unwind/$group: Mongo playground
Here’s the version with it: Mongo playground

Do you think the first one is better?

1 Like
4

That would depends on your specific traffic. But I prefer to avoid $unwind.

Thanks for sharing the 2 alternatives.

1 Like
5

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.