Query MongoDB nested document and find the exact matching inner object with having one of its filters taking its own field value as condition

Working with Data
,
1

I have the below document in a Mongo collection.

    {
   "_id":"112211",
   "student":{
      "student_display_name":"Siva",
      "first_name":"Siva",
      "last_name":"Ram",
      "age":"20",
      "department":"IT",
      "section":"D",
      "edit_sequence":"2",
      "unique_id":"siva_2",
      "address":{
         "data":[
            {
               "student_display_name":"Siva",
               "first_name":"Siva",
               "last_name":"Ram",
               "unique_id":"siva_2",
               "street":"Perter's Park",
               "area":"BMS Road",
               "pincode":"560001"
            },
            {
               "student_display_name":"Siva",
               "first_name":"Siva",
               "last_name":"Ram",
               "unique_id":"siva_1",
               "street":"St.Mary's Colony",
               "area":"BMS Road",
               "pincode":"560001"
            },
            {
               "student_display_name":"Siva",
               "first_name":"Siva",
               "last_name":"Ram",
               "unique_id":"siva_0",
               "street":"MG Colony",
               "area":"BMS Road",
               "pincode":"560001"
            }
         ]
      },
      "student":{
         "data":[
            {
               "student_display_name":"Siva",
               "first_name":"Siva",
               "last_name":"Ram",
               "age":"20",
               "department":"IT",
               "section":"B",
               "edit_sequence":"1",
               "unique_id":"siva_1"
            },
            {
               "student_display_name":"Siva",
               "first_name":"Siva",
               "last_name":"Ram",
               "age":"20",
               "department":"IT",
               "section":"A",
               "edit_sequence":"0",
               "unique_id":"siva_0"
            }
         ]
      }
   },
   "college":"student college",
   "root_table":"student"
}

From this document, I need to query using the following match filters.

{
   "$match":{
      "$or":[
         {
            "student.address.data.pincode":"56001"
         },
         {
            "$and":[
               {
                  "student.address.data.data.last_name":"Siva"
               },
               {
                  "student.address.data.data.first_name":"Ram"
               }
            ]
         }
      ]
   }
}

With these match filters, Further,
We will get all the 3 objects under address.data array.
But, from these results I want to filter even further based on “student.unique_id” value, so that I will get only one match as below.

This is the final result which I want.

 {
       "address":{
                   "student_display_name":"Siva",
                   "first_name":"Siva",
                   "last_name":"Ram",
                   "unique_id":"siva_2",
                   "street":"Perter's Park",
                   "area":"BMS Road",
                   "pincode":"560001"
                }
    }

How to achieve this in MongoDB?

2

I see nothing that can be access with …data.data…. Certainly a typo and you have a extra data. Secondly,

"$and":[
               {
                  "student.address.data.data.last_name":"Siva"
               },
               {
                  "student.address.data.data.first_name":"Ram"
               }
            ]

with the typo corrected does not do what you except. If you want both conditions to be true for the same array element, you have to use https://docs.mongodb.com/manual/reference/operator/query/elemMatch/

See the difference here:

// starting collection
> db.array.find()
{ "_id" : 1, "a" : [ { "b" : 1, "c" : 2 } ] }
{ "_id" : 2, "a" : [ { "b" : 1, "c" : 3 }, { "b" : 2, "c" : 2 } ] }
> db.array.find( { "$and" : [ { "a.b" : 1 } , { "a.c" : 2 } ] } )
{ "_id" : 1, "a" : [ { "b" : 1, "c" : 2 } ] }
{ "_id" : 2, "a" : [ { "b" : 1, "c" : 3 }, { "b" : 2, "c" : 2 } ] }
// Note that the above is equivalent to the following due to implicit $and
> db.array.find( { "a.b" : 1 , "a.c" : 2 } )
{ "_id" : 1, "a" : [ { "b" : 1, "c" : 2 } ] }
{ "_id" : 2, "a" : [ { "b" : 1, "c" : 3 }, { "b" : 2, "c" : 2 } ] }
// and last what you really want
> db.array.find( { "a" : { "$elemMatch" : { "b" : 1, "c" : 2 } } } )
{ "_id" : 1, "a" : [ { "b" : 1, "c" : 2 } ] }
1 Like