$Lookup nested array element in aggregate

Question

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.

turivishal · Accepted Answer · 2021-05-14 05:13:15Z

1

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

answered May 14, 2021 at 5:13

turivishal

36.4k7 gold badges47 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

$Lookup nested array element in aggregate

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related