Nested lookup aggregation

Working with Data
, ,
1

I am working on a fairly big sveltekit project and use MongoDB for NodeJs. I have a serverless Atlas project. I try to get user data with the related user group data and with the (user group) related access data:

Example data:

users collection:

_id: ObjectId('641c689e0974d4617351897f'),
name: 'Bob',
email: 'bob@email.com',
user_group_ids: [
  0: 630ccd1a3a2da7d625cc7ccf
]

user_groups collection:

_id: ObjectId('630ccd1a3a2da7d625cc7ccf'),
name: 'Editor',
access: [
  0: {
    accessTargetId: 6443fc97748be7952dab922b
  }
]

access_tragets collection:

_id: ObjectId('6443fc97748be7952dab922b'),
name: 'posts',
url: '/posts'

This is my aggregation:

db.users.aggregate([
	{
		$match: {
			_id: ObjectId.createFromHexString('630ccd943a2da7d625cc7cd4'),
		},
	},
	{
		$lookup: {
			from: 'user_groups',
			localField: 'user_group_ids',
			foreignField: '_id',
			let: { userGroupId: '$access' },
			pipeline: [
				{
					$lookup: {
						from: 'access_targets',
						localField: 'userGroupId.accessTargetId',
						foreignField: '_id',
						as: 'access_targets_out',
					},
				},
			],
			as: 'user_groups',
		},
	}
]);

And I get this:

[
  {
    "name": "Bob",
    "email": "bob@email.com",
    "user_group_ids": [
      {
        "$oid": "630ccd1a3a2da7d625cc7ccf"
      }
    ],
    "user_groups": [
      {
        "_id": {
          "$oid": "630ccd1a3a2da7d625cc7ccf"
        },
        "name": "Editor",
        "access": [
          {
            "accessTargetId": {
              "$oid": "6443fc84748be7952dab922a"
            },
            "accessRightId": {
              "$oid": "644635c08de56feeeb600baa"
            }
          }
        ],
        "access_targets_out": []
      }
    ]
  }
]

Why is the ‘access_targets_out’ array empty?

The lookup and pipeline documentation states that nested lookups in pipelines are possible.

If I use

localField: ‘$$userGroupId.accessTargetId’,

which should be correct accoding to the documentation, I get an error message:

FieldPath field names may not start with ‘$’. Consider using $getField or $setField.

Any help is pretty much appreciated.

3

Hi does this line works?
let: { userGroupId: '$access' },

can you project that field and makesure its available and is performing as you expected?

1 Like
4

Hello :wave: @Frank_Herzog.

Welcome to the MongoDB Community forums :sparkles:

As mentioned by @Joby_Joseph, there might be an issue with the let: { userGroupId: '$access' }, line in your aggregation pipeline. However, based on the details you shared, I ran the following aggregation query:

db.users.aggregate([
  {
    $lookup: {
      from: "user_groups",
      localField: "user_group_ids",
      foreignField: "_id",
      pipeline: [
        {
          $lookup: {
            from: "access_targets",
            localField: "access.accessTargetId",
            foreignField: "_id",
            as: "access_targets_out",
          },
        },
      ],
      as: "user_groups",
    },
  },
])

and it returned the expected output as follows:

[
  {
    _id: ObjectId("6450e3514c46644e4df1b92e"),
    name: 'Bob',
    email: 'bob@email.com',
    user_group_ids: [ ObjectId("6450e3b54c46644e4df1b930") ],
    user_groups: [
      {
        _id: ObjectId("6450e3b54c46644e4df1b930"),
        name: 'Editor',
        access: [ { accessTargetId: ObjectId("6450e5104c46644e4df1b933") } ],
        access_targets_out: [
          {
            _id: ObjectId("6450e5104c46644e4df1b933"),
            name: 'Editor',
            access: {
              accessTargetId: ObjectId("6450f2584c46644e4df1b93b"),
              accessRightId: ObjectId("644635c08de56feeeb600baa")
            }
          }
        ]
      }
    ]
  }
]

Hope this helps. Feel free to let us know if you have any further questions.

Best,
Kushagra

5

Hi Kushagra,

great, that works. So, I don’t need to define a variable with ‘let’?
The documentations says:

The pipeline cannot directly access the joined document fields. Instead, define variables for the joined document fields using the let option and then reference the variables in the pipeline stages.

Thats a bit confusing. Maybe, I still don’t get around. Aggregation is very powerfull – and very complex to understand.

Cheers
Frank

6

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