Using $and query on array of embedded documents in mongodb [duplicate]

Question

I have a collection like this

/* 9 */
{
    "_id" : ObjectId("5a0da2f9a0d00b28dcc69b29"),
    "list_id" : ObjectId("59ec6c97a0d00bc96b5680d4"),
    "rowdatas" : [ 
        {
            "_id" : ObjectId("5a0da2f9a0d00b28dcc69b2a"),
            "field_id" : "59ec6d33a0d00bc96b5680d8",
            "field_name" : "Title2",
            "field_value" : "Arvind",
            "field_type" : "sltext"
        }, 
        {
            "_id" : ObjectId("5a0da2faa0d00b28dcc69b2b"),
            "field_id" : "59ec8a2ea0d00bc96b568111",
            "field_name" : "Department",
            "field_value" : "AS",
            "field_type" : "dropdown"
        }
    ]
}

/* 10 */
{
    "_id" : ObjectId("5a0da306a0d00b28dcc69b2c"),
    "list_id" : ObjectId("59ec6c97a0d00bc96b5680d4"),
    "rowdatas" : [ 
        {
            "_id" : ObjectId("5a0da306a0d00b28dcc69b2d"),
            "field_id" : "59ec6d33a0d00bc96b5680d8",
            "field_name" : "Title2",
            "field_value" : "Arnab",
            "field_type" : "sltext"
        }, 
        {
            "_id" : ObjectId("5a0da306a0d00b28dcc69b2e"),
            "field_id" : "59ec8a2ea0d00bc96b568111",
            "field_name" : "Department",
            "field_value" : "SD",
            "field_type" : "dropdown"
        }
    ]
}

/* 11 */
{
    "_id" : ObjectId("5a0da30fa0d00b28dcc69b2f"),
    "list_id" : ObjectId("59ec6c97a0d00bc96b5680d4"),
    "rowdatas" : [ 
        {
            "_id" : ObjectId("5a0da30fa0d00b28dcc69b30"),
            "field_id" : "59ec6d33a0d00bc96b5680d8",
            "field_name" : "Title2",
            "field_value" : "Ankush",
            "field_type" : "sltext"
        }, 
        {
            "_id" : ObjectId("5a0da30fa0d00b28dcc69b31"),
            "field_id" : "59ec8a2ea0d00bc96b568111",
            "field_name" : "Department",
            "field_value" : "SD",
            "field_type" : "dropdown"

        }
    ]
}

I'm trying to query this collection in a way that it returns only record no 9 and 10 that has the
"field_name" : "Department" and "field_value" : "SD"

The query I have tried is

 db.getCollection('ldata').find(
  { $and: [ 
                {"list_id": ObjectId("59ec6c97a0d00bc96b5680d4")} , 
                { 'rowdatas.field_name': 'Department', 'rowdatas.field_value': 'AS' }, 
           ] 
   }
 )

This returns the me 2 records correctly thats is 9 and 10. However, if I pass 'rowdatas.field_value': 'Arnab' it still returns me record no 10. This should return me zero result since I'm trying to find records with whose Department name is "SD" . In this case its matching the field_value without combining the field_name for the AND query.

I ran this query and it returns me only one record number 9. What exactly you are trying to achieve? — Aagam Jhaveri
– Aagam Jhaveri, Commented Nov 16, 2017 at 18:34
Also your record 9 has field_value AS and record 10 has field_value SD, so with AND condition only one of them would be returned in the query. — Aagam Jhaveri
– Aagam Jhaveri, Commented Nov 16, 2017 at 18:48
Unless you have other data in your database that you are not sharing with us, there is no way this query you have added returns record 9 and 10. It will only return record 9, as it has been already mentioned in the comments. — Alex P.
– Alex P., Commented Nov 16, 2017 at 19:08
Updated the question. The record in concern here is no 10. It has the value SD in department but the query value "Arnab" does not exist for department. So the AND query should fail. But it does not fail. Thats my concern. — Donny
– Donny, Commented Nov 16, 2017 at 20:13
See also Query for an Array Element that Meets Multiple Criteria in the core documentation. — Neil Lunn
– Neil Lunn, Commented Nov 16, 2017 at 21:18

Alex P. · Accepted Answer · 2017-11-17 12:39:13Z

You need to add $elemMatch to your query. So your query would look like this:

db.getCollection('ldata').find(
  { $and: [ 
                {"list_id": ObjectId("59ec6c97a0d00bc96b5680d4")} , 
                { rowdatas: {$elemMatch: {'field_name': 'Department', 'field_value': 'Arnab' }}}, 
           ] 
   }
 )

This would return no results.

To explain it a little bit better. What your query does:

$and: [ 
                {"list_id": ObjectId("59ec6c97a0d00bc96b5680d4")} , 
                { 'rowdatas.field_name': 'Department', 'rowdatas.field_value': 'Arnab' }, 
           ] 
   }

It simply says, find any document where the array rowdatas has a field_name: 'Department', and a field_value: 'Arnab'. This is true for document 10, because it looks inside the complete array rowdata.

If you now add the $elemMatch query operator like that:

{ rowdatas: {$elemMatch: {'field_name': 'Department', 'field_value': 'Arnab' }}}

Then you are simply saying you want both these fields (field_name and field_value) to match in a single array entry.

Hope you understand the difference between querying an array field through a dot (rowdatas.field_name) and $elemMatch (rowdatas: {$elemMatch: {'field_name': ...) a little better now.

Collectives™ on Stack Overflow

Using $and query on array of embedded documents in mongodb [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related