Mongodb multi nested array search

Question

My aim is to search records of data userid 1

Below is my data

{ "_id" : 2,
  "name" : "test", 
  "data" :[{"_id" : "1","file" : "nic", "userid" : [1,2 ]},
           {"_id" : "2","file" : "nic1","userid" : [1 ]  },
           {"_id" : 3,"file" : "nick2","userid" : [1,2 ]} 
     ]},

{ "_id" : 3,
  "name" : "test",
  "data" : [{"_id" : "1","file" : "nic","userid" : [1,2 ]  },
            {"_id" : "2","file" : "nic1", "userid" : [3,2 ] } 
      ]}

out put should be

{ "_id" : 2,
  "name" : "test", 
  "data" :[{"_id" : "1","file" : "nic", "userid" : [1,2 ]},
           {"_id" : "2","file" : "nic1","userid" : [1 ]  },
           {"_id" : 3,"file" : "nick2","userid" : [1,2 ]} 
     ]},

{ "_id" : 3,
  "name" : "test",
  "data" : [{"_id" : "1","file" : "nic","userid" : [1,2 ]  },          
      ]}

I tried

$res=$collection->find(array("data.userid" =>array('$in'=>array('52'))));

returning null

Blakes Seven · Accepted Answer · 2015-10-15 09:16:28Z

1

You need the .aggregate() method in order to "filter" any array content for more than a singular match, and also the basic match is a lot simplier as MongoDB does not care that the data is within arrays, just as long as the specified path is correct:

db.collection.aggregate([
    { "$match": { "data.userid": 1 } },
    { "$project": {
        "data": {
            "$setDifference": [
                { "$map": {
                    "input": "$data",
                    "as": "el",
                    "in": { 
                        "$cond": [
                            { "$setIsSubset": [ [1], "$$el.userid" ] },
                            "$$el",
                            false
                        ]
                    }
                }},
                [false]
            ]
        }
    }},
    { "$match": { "data.0": { "$exists": true } }}
])

With PHP this notates as follows:

$collection->aggregate(array(
    array( '$match' => array( "data.userid" => 1 )),
    array(
        '$project' => array(
            'data' => array(
                '$setDifference' => array(
                    array(
                        '$map' => array(
                            'input' => '$data',
                            'as' => 'el',
                            'in' => array(
                                '$cond' => array(
                                    array( '$setIsSubset' => array(array(1),'$$el.userid') ),
                                    '$$el',
                                    FALSE
                                )
                            )
                        )
                    ),
                    array(FALSE)
                )
            )
        )
    ),
    array( '$match' => array( 'data.0' => array( '$exists' => TRUE ) ) )
))

The $map operator allows inspection of each element ofthe outer array and passed each element to the $cond ternary operation. This processes a $setIsSubset operation on the "inner" array to see if it in fact contains one of the values in the alternate set ( in this case [1] ) and where a true evaluation is made then the element is returned or otherwise false.

The point of $setDifference is to remove those false values from the modified array and only return matched elements. And finally the $exists test looks to see that outer array actually has at least one element and is not empty as a result of the filtering.

The documents returned are the ones with the matching condition and only the array elements that also match the specified condition.

Of course the operators here require that you have at least MongoDB 2.6 as the server ( which is quite an old release now and an advised update at least ) but if you still have a lesser version then you need a traditional approach with $unwind and $group:

$collection->aggregate(array(
    array( '$match' => array( "data.userid" => 1 )),
    array( '$unwind' => '$data' ),
    array( '$match' => array( 'data.userid' => 1 )),
    array( 
        '$group' => array(
            '_id' => '$_id',
            'data' => array( '$push' => '$data' )
        )
    )
))

edited Oct 15, 2015 at 9:16

answered Oct 15, 2015 at 8:46

Blakes Seven

50.5k14 gold badges132 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sasi kanth Over a year ago

getting error exception: invalid operator '$setDifference'

Blakes Seven Over a year ago

@sasikanth That means that you have quite an old version of MongoDB ( less than 2.6 ) which does not have those operators. There is an alternate ( and not as performant ) method to this that works in all versions. MongoDB 2.6 is a few years old now and there are many reasons why an upgrade should be considered.

sasi kanth Over a year ago

thanks for helping . I will update my version and check that once. I need one more help. I want to display all records in data as loop how can i do that

Blakes Seven Over a year ago

@sasikanth Please don't update your question as this is an anwer to the question that was asked. If you have addtional questions then Ask Another Question.

sasi kanth Over a year ago

blake could you check this lick once http://stackoverflow.com/questions/33174252/mongodb-multi-nested-array-addtoset-push-pull

Collectives™ on Stack Overflow

Mongodb multi nested array search

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related