CODEWITHSUNDEEP
jsonmongodb
Pradeep Charan
Pradeep Charan

Reputation: 683

Mongodb select the documents where key exists in all JSON objects in JSON Array

My documents in MongoDB has following JSON structure. How Can I write a query to select the documents that have JSON Array "test" and array is not empty and all the JSON Objects inside that array have key "attr3".

{
"id": "123456",
"test": [
     {
        "att1": 4,
        "att2": "fffff",
        "att3": 46
        },
     {
        "att1": 8,
        "att2": "ggggg",
        "att3": 6
    },
     {
        "att1": 3,
        "att2": "hhhh",
        "att3": 4
        },
     {
        "att1": 4,
        "att2": "llll",
        }
  ]
}

The above document must not be retrieved because it has JSON Array "test" but does not have "attr3" for last object.

Upvotes: 1

Views: 729

Answers (1)

Saravana
Saravana

Reputation: 12817

you can check the $size of test array and $size of test.att3 array, if both are same then all array elements contains the attribute att3

{$expr: 
    {$and:[
        "$test", //exists
        {$gt: [{$size: "$test"},0]}, //non empty
        {$eq:[{$size: "$test"},{$size: "$test.att3"}]} //att3 present in all
    ]}
}

collection

> db.t66.find()
{ "_id" : ObjectId("5c47df42efd14747b5de90f8"), "test" : [ { "att3" : "sda" }, { "att3" : "gsfe" }, {  }, { "att3" : "" } ] }
{ "_id" : ObjectId("5c47dfbfefd14747b5de90f9"), "test" : [ { "att3" : "sda" }, { "att3" : "gsfe" }, { "att3" : "fewfw" }, { "att3" : "" } ] }
{ "_id" : ObjectId("5c47e0fbefd14747b5de90fa") }

find

> db.t66.find({$expr : {$and: [{$gt : [{$size : {$ifNull : ["$test", []]}},0]},{$eq:[{$size : "$test"},{$size : "$test.att3"}]}]}})
{ "_id" : ObjectId("5c47dfbfefd14747b5de90f9"), "test" : [ { "att3" : "sda" }, { "att3" : "gsfe" }, { "att3" : "fewfw" }, { "att3" : "" } ] }

aggregate

> db.t66.aggregate([{$match : {$expr : {$and: [{$gt : [{$size : {$ifNull : ["$test", []]}},0]},{$eq:[{$size : "$test"},{$size : "$test.att3"}]}]}}}])
{ "_id" : ObjectId("5c47dfbfefd14747b5de90f9"), "test" : [ { "att3" : "sda" }, { "att3" : "gsfe" }, { "att3" : "fewfw" }, { "att3" : "" } ] }
>

Upvotes: 1

Related Questions