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.

score 0 · Answer 1 · answered Apr 19 '23 at 09:06

0

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.

answered Apr 19 '23 at 09:06

Charchit Kapoor

8,934
2
8
24

Thank you so much for the response. I need to read $reduce, and the the usage of double $$ to understand further. I shall do the same and get back when I have questions. Meanwhile, I want to get the output for a particular category_id. I tried writing a $match in the aggregation : ```db.collection.aggregate([ { "$match": { "category_id": "CC_100" }, }, { "$project": { //rest of the query } ])``` This doesnt seem to generate any output. Cant figure out why. Could you please help me understand. – bazinga Apr 19 '23 at 09:31
The category_id in the question is `CC100`, the playground also has the same value. @bazinga. – Charchit Kapoor Apr 19 '23 at 09:44
[Updated Playground Link](https://mongoplayground.net/p/5ghG0jws0i7) If you see this input data, you can see that the records have 'CC100' and 'CC101' as the ```category_id``` values. I want the ```sub_version_code``` of ```category_id``` = `CC101' alone. That is what I wanted to do with the ```$match``` in the previous comment and which I was failing to achieve – bazinga Apr 19 '23 at 09:52
It seems to be working https://mongoplayground.net/p/oUsdxtaRZEM. @bazinga – Charchit Kapoor Apr 19 '23 at 09:58

MongoDB Query to get deeply nested field from a collection

1 Answers1