MongoBb - Aggregation step to project elements from matching nested array document to root document level

Question

I've the following structure of documents in MongoDB. The array records contain different persons that have the roles teacher or student.

[
    {_id: 1, persons: [{role:"teacher", name: "Mark", school: "", county: "Oneida"}, {role:"student", name: "John", school: "LM High School", county:""}]},
    {_id: 2, persons: [{role:"teacher", name: "Robert", school: "", county: "Onondaga"}, {role:"student", name: "David", school: "Beaker High School", county:""}}]}
]

I need to get the output in the following format. From the first matching teacher person I need to select county, and from the first matching student person I need to select the school. I need to project both school and county from the nested array element level to the root document level.

[
    {_id: 1, county: "Oneida", school: "LM High School"},
    {_id: 2, county: "Onondaga", school: "Beaker High School"}
]

What can be the appropriate aggregation step? I tried several steps like arrayElemAt, projection, etc. but couldn't get the correct one.

turivishal · Accepted Answer · 2021-07-09 04:17:38Z

1

$filter to iterate loop of persons array and filter teachers and students separately
to get the first element from the array, use $first from MongoDB 4.4, for below you can use $arrayElemAt

db.collection.aggregate([
  {
    $project: {
      teacher: {
        $filter: {
          input: "$persons",
          cond: { $eq: ["$$this.role", "teacher"] }
        }
      },
      student: {
        $filter: {
          input: "$persons",
          cond: { $eq: ["$$this.role", "student"] }
        }
      }
    }
  },
  {
    $project: {
      county: { $first: "$teacher.county" },
      school: { $first: "$student.school" }
    }
  }
])

Playground

edited Jul 9, 2021 at 4:17

answered Jun 6, 2021 at 15:56

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

2 Comments

Martin Borugadda Over a year ago

Thank you! For the second step $first didn't work for me. I had to use something like county: { $arrayElemAt: ["$teacher.county",0] }

turivishal Over a year ago

okay, $first will work from mongodb v4.4.

Collectives™ on Stack Overflow

MongoBb - Aggregation step to project elements from matching nested array document to root document level

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related