Querying and Filtering Nested Arrays in Mongo DB (Shell)

Question

I have the following 3 documents in my mongo collection

{ 
    "_id" : {
        "docId" : "089b0b4e-3096-4111-b074-a6713f58bfab"
    }, 
    "items" : [
        {
            "products" : [ 
                {
                    "_id" : {
                        "productId" : null
                    }
                }, 
                {
                    "_id" : {
                        
                    }
                }, 
                {
                    "_id" : {
                        "productId" : "c26672a8-b5b1-42bf-8ec5-476db4bd3975"
                    }
                }
            ]
        },
        {
            "products" : [
                {
                    "_id" : {
                        "productId" : "681a8658-768a-4665-bf51-ef556f881646"
                    }
                }, 
                {
                    "_id" : {
                        
                    }
                }, 
                {
                    "_id" : null
                }
            ]
        }
    ]
},
{ 
    "_id" : {
        "docId" : "32d2e389-d377-4552-a3c6-dc52d2abaa6c"
    }, 
    "items" : [
        {
            "products" : [ 
                {
                    "_id" : {
                        "productId" : null
                    }
                }, 
                {
                    "_id" : {
                        
                    }
                }, 
                {
                    "_id" : {
                        "productId" : "bea6baef-d8c0-4e7c-9b49-98af4a4f53e8"
                    }
                }
            ]
        },
        {
            "products" : [
                {
                    "_id" : {
                        "productId" : null
                    }
                }, 
                {
                    "_id" : {
                        "productId" : "8d598e72-cd72-4d3b-aa2c-1278ad4acce1"
                    }
                }, 
                {
                    "_id" : null
                }
            ]
        }
    ]
},
{ 
    "_id" : {
        "docId" : "69effab9-2071-4c9b-a49d-dd38384991d9"
    }, 
    "items" : [
        {
            "products" : [ 
                {
                    "_id" : {
                        "productId" : null
                    }
                }, 
                {
                    "_id" : {
                        
                    }
                }
            ]
        }
    ]
}

Assuming a valid productId is one that exists and is not null, I need my query to return only the documents that contain at least one valid productId and at the same time the products array should only include those valid product documents only like so: (Note that only 2 documents are returned with a total of 4 products)

{ 
    "_id" : {
        "docId" : "089b0b4e-3096-4111-b074-a6713f58bfab"
    }, 
    "items" : [
        {
            "products" : [ 
                {
                    "_id" : {
                        "productId" : "c26672a8-b5b1-42bf-8ec5-476db4bd3975"
                    }
                }
            ]
        },
        {
            "products" : [
                {
                    "_id" : {
                        "productId" : "681a8658-768a-4665-bf51-ef556f881646"
                    }
                }
            ]
        }
    ]
},
{ 
    "_id" : {
        "docId" : "32d2e389-d377-4552-a3c6-dc52d2abaa6c"
    }, 
    "items" : [
        {
            "products" : [ 
                {
                    "_id" : {
                        "productId" : "bea6baef-d8c0-4e7c-9b49-98af4a4f53e8"
                    }
                }
            ]
        },
        {
            "products" : [
                {
                    "_id" : {
                        "productId" : "8d598e72-cd72-4d3b-aa2c-1278ad4acce1"
                    }
                }
            ]
        }
    ]
}

I tried this but does not seem to work

collection.find({    
    "items": {
         $elemMatch: {
             "products": {
                 $elemMatch: {
                    "_id.productId": { $exists: true, $ne: null }
                 }
             }
         }   
    }
})

Is this not possible to do with the find method?

user_7975771 · Answer

You may go with aggregations

Your productId is inside the nested array. We use $map to loop it.
$filter to filter the objects using condition
If your products array has any matching element to the condition ($anyElementTrue), we add another variable hasProductId is true or false with teh help of $mergeObjects
The condition is productId cannot be null and undefined
You will get hasProductId in each objects of document
$match helps to if anything true, then get the document
$project helps to remove the temp field $hasProductId

here is the code

db.collection.aggregate([
  {
    $project: {
      items: {
        "$map": {
          "input": "$items",
          "in": {
            products: {
              "$filter": {
                "input": "$$this.products",
                "as": "product",
                "cond": {
                  "$and": [
                    {
                      $ne: [
                        "$$product._id.productId",
                        null
                      ],
                      
                    },
                    {
                      $ne: [
                        "$$product._id.productId",
                        undefined
                      ],
                      
                    }
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "$match": {
      "items.products": {
        $ne: []
      }
    }
  }
])

Working Mongo playground

Querying and Filtering Nested Arrays in Mongo DB (Shell)

1 Answers