I have a mongodb document in this format. There are multiple collections of arrays as given here. How do I get all elements where SID = 100.

{
  "_id" : "123456", 
    "Continent" : {
        "Country" : [
            [
                US, 
                {
                    "State" : [
                        [
                            100, 
                            {
                                "Product" : "Corn",  
                                "SID" : 100
                            }
                        ], 
                        [
                            200, 
                            {
                                "Product" : "Maze",  
                                "SID" : 200
                            }
                        ]
            ],
                }
            ]
        ], 
    }, 
}

CodePudding user response：

Option 1:

db.collection.aggregate([
 {
   $unwind: "$Continent.Country"
 },
 {
   $unwind: "$Continent.Country"
 },
 {
   $unwind: "$Continent.Country.State"
 },
 {
   $unwind: "$Continent.Country.State"
 },
 {
  $match: {
    "Continent.Country.State.SID": 100
 }
 },
 {
  $project: {
    "myProductWithSID100": "$Continent.Country.State"
   }
 }
])

Explained:

1. Unwind all arrays and nested arrays.
2. Match only the subobjects with SID:100
3. Project only the necessary object content

Playground1

Option 2: ( Slightly faster)

db.collection.aggregate([
{
  $unwind: "$Continent.Country"
},
{
  $unwind: "$Continent.Country"
 },
{
  $unwind: "$Continent.Country.State"
},
{
  $match: {
    "Continent.Country.State.SID": 100
 }
},
{
 "$addFields": {
  "Continent.Country.State": {
    "$filter": {
      "input": "$Continent.Country.State",
      "as": "c",
      "cond": {
        $eq: [
          "$$c.SID",
          100
        ]
       }
     }
    }
  },
  {
    $unwind: "$Continent.Country.State"
  },
  {
    $project: {
      "myProductWithSID100": "$Continent.Country.State"
   }
  }
 ])

Explained:

1. Unwind the first 3x arrays
2. Match only documents having at least 1x SID:100 in 4th array
3. Filter only those from 4th array having SID:100
4. Unwind the 4th array
5. Project only the final object we need

Playground2