CODEWITHSUNDEEP
mongodbaggregation-frameworklookup
Chuanyu Wang
Chuanyu Wang

Reputation: 47

$Lookup nested array element in aggregate

I have two collects students and classes, and I want to query the all students with their free classes.

Mongo Playground at https://mongoplayground.net/p/WwON7lHbAvn

Collection students

[
    {
      "_id": 1,
      "name": "John"
    },
    {
      "_id": 2,
      "name": "Nancy"
    }
  ]

Collection classes:

[
    {
      type: "free",
      "enrollment": [
        {
          studentID: 1,
          other: "other value"
        }
      ]
    },
    {
      type: "common",
      "enrollment": [
        {
          studentID: 1,
          other: "other value"
        },
        {
          studentID: 2,
          other: "other value"
        }
      ]
    }
  ]

My Query:

db.students.aggregate([
  {
    $lookup: {
      from: "classes",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            type: "free",
            $expr: {
              "enrollment.studentID": "$$id"
            }
          }
        }
      ],
      as: "freeclasses"
    }
  }
])

But it gives me the error FieldPath field names may not contain '.'.

Also demonstrate at https://mongoplayground.net/p/WwON7lHbAvn

Many thanks for any help or suggestion.

Upvotes: 1

Views: 768

Answers (1)

turivishal
turivishal

Reputation: 36154

FieldPath field names may not contain '.'.

The error comes from $expr: { "enrollment.studentID": "$$id" }, this is invalid syntax for $expr, to fix this,

$expr: { $eq: ["$enrollment.studentID", "$$id"] }

But this just resolved your error, there is another issue the enrollment is an array and when we check any condition with field enrollment.studentID will return array of ids, so you can use $in instead of $eq,

Final solution is,

$expr: { $in: ["$$id", "$enrollment.studentID"] }

Playground

Upvotes: 1

Related Questions