How to unwind nested object arrays in mongodb aggregation?

Question

I have some data with an array of objects which contains another array. I am trying to unwind the courses array then the requirements array to match subjects then group everything back together.

So far I can work fine with the courses array. But I just can't get around doing the same thing for the second array requirements.

[
  {
    "_id": 1,
    "name": "provider name",
    "courses": [
      {
        "_id": 1,
        "name": "course name",
        "requirements": [
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          },
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          }
        ]
      }
    ]
  }
]

First I unwind both arrays

  {
    $unwind: {
      path: '$courses',
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $unwind: {
      path: '$courses.requirements',
      preserveNullAndEmptyArrays: true
    }
  },

How I group the first array

  {
    $group: {
      _id: '$_id',
      root: { $mergeObjects: '$$ROOT' },
      courses: { $push: '$courses' }
    }
  },
  {
    $replaceWith: {
      $mergeObjects: ['$root', '$$ROOT'],
    }
  },
  {
    $project: { root: 0 }
  },

Expected Output

[
  {
    "_id": 1,
    "name": "provider name",
    "courses": [
      {
        "_id": 1,
        "name": "course name",
        "requirements": [
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          },
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          }
        ]
      }
    ]
  }
]

But I get

[
  {
    "_id": 1,
    "name": "provider name",
    "courses": [
      {
        "_id": 1,
        "name": "course name",
        "requirements": [
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          },
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          }
        ]
      },
      {
        "_id": 1,
        "name": "course name",
        "requirements": [
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          },
          {
            "subjects": [
              "SubjectA::B",
              "SubjectB::B",
              "SubjectC::B"
            ]
          }
        ]
      }
    ]
  }
]

turivishal · Accepted Answer · 2021-04-27 06:35:57Z

4

You can reconstruct tour arrays by below stages,

$group by _id and courses._id and get first name and course first name, and construct the requirements array
$group by only _id and get first name and reconstruct courses array

  {
    $group: {
      _id: {
        _id: "$_id",
        cId: "$courses._id"
      },
      name: { $first: "$name" },
      course_name: { $first: "$courses.name" },
      requirements: { $push: "$courses.requirements" }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      name: { $first: "$name" },
      courses: {
        $push: {
          _id: "$_id.cId",
          name: "$course_name",
          requirements: "$requirements"
        }
      }
    }
  }

Playground

answered Apr 27, 2021 at 6:35

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

7 Comments

ABZO Over a year ago

Great, that really helped. So if I run this pipeline on thousands of records would that involve a high cost on the database?

turivishal Over a year ago

what you mean by high cost? if you are talking about performance its ok if you have more documents in root, but it will affect if you have more nested documents.

ABZO Over a year ago

Yes, performance, my expectations after the first few match stages will end up with less than a hundred for each courses array and around 2 to 3 in the requirements array. would this approach be okay performance-wise or it is not recommended.

turivishal Over a year ago

it will affect performance issue but, can you explain and share what match are you doing before group stage. so i will share another query without unwind and group stage. so after that you can compare both queries performance.

turivishal Over a year ago

i can understand but can you share what filtering are you doing before $group stage, can you add that in playground and share me new link.

|

Collectives™ on Stack Overflow

How to unwind nested object arrays in mongodb aggregation?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related