MongoDB Query to get deeply nested field from a collection

Question

I have a table that looks as given below:

[
    {
        _id: "689000",
        category_id: "CC100",
        versions: [
            {
                version_id: 001,
                sub_versions: [
                    {
                        sub_version_code: PS, 
                        verification_name: test
                    },{
                        sub_version_code: PPS, 
                        verification_name: test
                    },
                ]
            },
            {
                version_id: 002,
                sub_versions: [
                    {
                        sub_version_code: BS, 
                        verification_name: test
                    },{
                        sub_version_code: BBS, 
                        verification_name: test
                    },
                ]
            },
        ]
    },{
        _id: "689001",
        category_id: "CC100",
        versions: [
            {
                version_id: 001,
                sub_versions: [
                    {
                        sub_version_code: PS, 
                        verification_name: test
                    },{
                        sub_version_code: PPS, 
                        verification_name: test
                    },
                ]
            },
            {
                version_id: 002,
                sub_versions: [
                    {
                        sub_version_code: BS, 
                        verification_name: test
                    },{
                        sub_version_code: BBS, 
                        verification_name: test
                    },
                ]
            },
        ]
    }
]

The above is a sample of the table structure. Multiple records in the collection can have the same category_id. The motive is to query the collection with the category_id and return only the sub_versions_code .

The expected o/p:

[
    {
        _id: "689000",
        codes: [
            sub_version_code: PS,
            sub_version_code: PPS,      
            sub_version_code: BS, 
            sub_version_code: BBS, 
        ]
    },{
        _id: "689001",
        codes: [
            sub_version_code: PS, 
            sub_version_code: PPS, 
            sub_version_code: BS, 
            sub_version_code: BBS, 
        ]
    },
]

Since Im just learning MongoDB, Im getting quite confused about aggregate function and how to integrate it into getting a deeply nested field from a collection.

Answer 1

You can write an aggregation pipeline like this, loop over versions array using $reduce, and map the subversions to an array of codes and concatenate it with the reduced value:

db.collection.aggregate([
  {
    "$project": {
      _id: 1,
      codes: {
        "$reduce": {
          "input": "$versions",
          "initialValue": [],
          "in": {
            "$concatArrays": [
              "$$value",
              {
                "$map": {
                  "input": "$$this.sub_versions",
                  "as": "item",
                  "in": "$$item.sub_version_code"
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground link.