Mongodb: Querying array of subdocuments

Question

I have users' collection whose schema is like:

{
  _id: unique number,
  name: 'asdf',
  age: '12',
  gender: 'm',
  address: [
    {area: 'sdf',
     city: 'sdq', 
     state: 'wfw'},
    {area: 'asdf',
     city: 'sdfs',
     state: 'vfdwd'}
  ]
}

I want to find out the users for whom all the values of state in address should be the value I pass. If even one of the state value doesn't match with the value I pass the user shouldn't be returned. I tried simple find, aggregation framework with $unwind, $match but nothing seemed to get solution. Can you please help me out...

Thanks

P.S. please bear with multiple addresses for the sake of question. :)

score 2 · Answer 1 · answered Dec 10 '14 at 17:31

2

To find out if all array entries match the state "wfw", do an aggregation like the following:

db.users.aggregate([
    { "$project" : {
        "test" : {
            "$allElementsTrue" : [{
                "$map" : { 
                    "input" : "$address", 
                    "as" : "a", 
                    "in" : { "$eq" : ["wfw", "$$a.state"] }
                }
            }]
        }
    } },
    { "$match" : { "test" : true } }
])

This aggregation takes each document, maps "state equals 'wfw'" over the address array to get a boolean array, and tests if the entire array is true, storing the result in `test, and then filtering the results based on test. You will need MongoDB 2.6 for support of some of the operators.

answered Dec 10 '14 at 17:31

wdberkeley

11,531
1
28
23

What different with $all? – Barno Dec 10 '14 at 18:42
`$all` checks that all the elements of the array argument to `$all` are present in the array field, not that all of the elements of the array field are among the elements of the array argument to `$all`. For example, `{ "$all" : [1, 2, 3] }` matches `[1, 2, 3, 4]`, not `[1, 2]`. It's not what the OP needs. – wdberkeley Dec 10 '14 at 20:00
hi wdberkeley. thanks for your solution. I tried it, but it shows following exception. Can you figure out why- failed: exception: $allElementsTrue's argument must be an array, but is NULL – harshad Dec 11 '14 at 05:26
Also I have one doubt: can we have multiple $match stages in aggregation? I have this condition where result of one query should be forwarded to next query & so on (because queries are prioritized) & those queries are of comparison in nature. Aggregation framework seems to be best suitable solution for this type of problem, (because of pipeline). But if there is any other way you can suggest I would appreciate it. – harshad Dec 11 '14 at 05:48
Yup, you can have multiple `$match` stages. It's best to condense into one query if possible, I'd say, because only the first match will be able to use indexes. – wdberkeley Dec 11 '14 at 16:26

Barno · Accepted Answer · 2014-12-11T10:35:42.163

1

I don't know if I understand.

I replicated your document. When you want to retrieve an user by state you can do in many ways

If you search with single value you can do

db.g.find({ "address.state": "wfw" })

and retrieve an user

You can use $all

db.g.find( { "address.state": { $all: ["wfw","vfdwd"] } } ) // retrieve User

db.g.find( { "address.state": { $all: ["wfw","vfdwd","foo"] } } ) // don't retrieve User

or you can use $and

db.g.find( { $and: [ { "address.state":"wfw" },{ "address.state":"vfdwd" }] } )

But I don't know if I understand your question

Update and the correct answer

db.g.find( { "address.state": { $nin: ["wfw"] } } )

Let me Know

edited Dec 11 '14 at 10:35

answered Dec 10 '14 at 15:38

Barno

3,271
5
28
58

Hi Barno. This is what I had tried. But it didn't seem to give me desired solution. Anyway thanks for your help. Regarding the question, I want to retrieve the users who don't have any of the state address matching the value I pass. For eg- users who haven't stayed in the state 'wfw' yet. In this case the query should return me the users who don't have any of the state address matching 'wfw'. Got it? – harshad Dec 11 '14 at 05:43
@harshad Maybe Now I understand. Something like `db.g.find( { "address.state": { $nin: ["wfw"] } } )` ? – Barno Dec 11 '14 at 09:25
Thank you very much Barno. You got it! It seems to work. Got a relief. :) – harshad Dec 11 '14 at 10:34
nice ! http://docs.mongodb.org/manual/reference/operator/query/nin/ you are welcome – Barno Dec 11 '14 at 10:36

Mongodb: Querying array of subdocuments

2 Answers2