[SERVER-2989] $elemMatch for querying nested objects Created: 22/Apr/11  Updated: 06/Dec/22  Resolved: 16/Mar/18

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Dustin Smith Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 6
Labels: query
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Feature missing from db version v1.9.0-pre-, pdfile version 4.5
git version: c440cb9ddaa9cde91f58f35f24d6ac4d2583a016


Issue Links:
Duplicate
duplicates SERVER-267 Wildcard support in index/query/proje... Backlog
Related
related to SERVER-736 Support for XPath like queries Backlog
related to SERVER-267 Wildcard support in index/query/proje... Backlog
is related to MONGOID-4622 Document recursively_embeds_many filt... Closed
Assigned Teams:
Query
Participants:

 Description   

Suppose you have several nested objects and lists:

> db.test.insert({"a" : { "b" : { "c" :

{ "d1" : [ "e1" ], "d2" : [ "e2" ], "d3" : [ "e3", "e4" ], "d4" : [ "e5", "e6" ] }

} } })
> db.test.find({'a.b.c' : {$exists : true}})
{ "_id" : ObjectId("4daf2ccd697ebaacb10976ec"), "a" : { "b" : { "c" :

{ "d1" : [ "e1" ], "d2" : [ "e2" ], "d3" : [ "e3", "e4" ], "d4" : [ "e5", "e6" ] }

} } }

I want to retrieve this document based on the sub-object value:

{'d2': ["e3":"e4"]}

or just the existence of the array ["e1"] without knowing the value key 'd1'.
The depth of the structure remains constant, but the keys are unknown.

I thought that chaining a few $elemMatch would work, but it did not retrieve the desired results:

> db.test.find({'a.b': {$elemMatch : {$elemMatch : {$all : ["e1"] }}}})
> db.test.find({'a.b': {$elemMatch : {$elemMatch : {$elemMatch : {$all : ["e1"] }}}}})



 Comments   
Comment by Asya Kamsky [ 16/Mar/18 ]

On second reading, it might be more similar to SERVER-736 which asks about querying for particular component at unknown depth of the document.

Comment by Asya Kamsky [ 16/Mar/18 ]

This looks like it would be addressed by the same thing SERVER-267 is asking for, so I'm going to close it as a duplicate.

Comment by Asya Kamsky [ 05/Dec/17 ]

In just released 3.6.0 it's also possible to query for such documents using the new $jsonSchema operator (which means this can also be enforced via schema validation):

db.coll.find( {$jsonSchema:{
    "properties" : {
        "users" : {
            "type" : "object",
            "additionalProperties" : {
                "type" : "object",
                "properties" : {
                    "admin" : { "type" : "boolean"}
                }
            },
            "not" : {
                "additionalProperties" : {
                    "properties" : {
                        "admin" : { "enum" : [false]}
                    }
                }
            }
        }
    }
}

Comment by Asya Kamsky [ 04/Dec/17 ]

Depending on exact use case, this can be done via aggregation pipeline syntax (different expressions may apply in different scenarios).

For the example given of users who may have admin where the username is the key, it's possible to use this syntax:

db.coll.aggregate([
   {$addFields:{u:{$objectToArray:"$users"}}},
   {$match:{"u.v.admin":true}},
   {$project:{u:0}}
])

In the upcoming version 3.6.0 it's possible to do this in "find" and "update" via new $expr which allows using aggregation expressions in find:

db.coll.find(
     {$expr:{$in:[
        true, 
        {$map:{input:{$objectToArray:"$users"},in:"$$this.v.admin"}}
     ]}}
)

A related ticket to this is SERVER-267 which requests syntax that would make this simpler as well as support for indexes for wildcard queries.

Comment by David Jhanyan [ 28/Apr/15 ]

this feature will delight me

Comment by kic [ 08/Sep/13 ]

here is another one:

consider you have an element
{
_id : 1,
"name" :

{"en" : "hello world", "de" : "hallo welt"}

}

what here is pretty cool, is that you can upsert languanges and you do not have to know anything about a language so i.e.

db.test.update({_id:41,{$set : {"name.fr" : "bonjour tout le monde"}},

{ upsert: true }

);

pretty cool huh? but the draw back is you can not search across languanges

db.things.find(

{"name.*" : /.*world.*/}

)

Once you have the idea of using arrays to seperate languages, you will loose the upsert feature, since

db.test.update({_id:2},{$set : {"name.2" : "hallo welt"}},

{ upsert: true }

);

will result in
{ "_id" : 2, "name" :

{ "2" : "hallo welt" }

}

and not like expected in

{ "_id" : 2, "name" : [ null, "hallo welt" ] }

Comment by Jason Denizac [ 11/May/13 ]

Eliot, I spoke with you today at MongoSF about this. Here's the usecase I have:
{users: {a:

{admin: false}

, b:

{admin: false}

, c: {admin: true}}

I want to be able to db.collection.find(

{'users.*.admin': true}

)

If this were an array, I could do db.collection.find({users: {$elemMatch: {admin: true}})

Comment by Eliot Horowitz (Inactive) [ 23/Apr/11 ]

Not sure this is the way well go with this, there are a number of possibilities.

Generated at Thu Feb 08 03:01:45 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.