Mongodb nested array search

Question

document structure example is:

{
   "dob": "12-13-2001",
   "name": "Kam",

   "visits": {
     "0": {
       "service_date": "12-5-2011",
       "payment": "40",
       "chk_number": "1234455",  
    },
     "1": {
       "service_date": "12-15-2011",
       "payment": "45",
       "chk_number": "3461234",  
    },
     "2": {
       "service_date": "12-25-2011",
       "payment": "25",
       "chk_number": "9821234",  
    } 
  } 
}


{
   "dob": "10-01-1998",
   "name": "Sam",

   "visits": {
     "0": {
       "service_date": "12-5-2011",
       "payment": "30",
       "chk_number": "86786464",  
    },
     "1": {
       "service_date": "12-15-2011",
       "payment": "35",
       "chk_number": "45643461234",  
    },
     "2": {
       "service_date": "12-25-2011",
       "payment": "20",
       "chk_number": "4569821234",  
    } 
  } 
}

In PHP i want to list all those "visits" information (and corresponding "name" ) for which payment is less than "30".

I want to print only the visits with "payment" < "30" not others. Is such query possible, or do i have to get entire document first using search and then use PHP to select such visits??

Solution 1

In the example document, the "payment" values are given as strings which may not work as intended with the $lt command. For this response, I have converted them to integers.

Wildcard queries are not possible with MongoDB, so with the given document structure, the key (0,1,2, etcetera) of the sub-document must be known. For instance, the following query will work:

> db.test.find({"visits.2.payment":{$lt:35}})

However,

> db.test.find({"visits.payment":{$lt:35}})

Will not work in this case, and

> db.test.find({"visits.*.payment":{$lt:35}})

will also not return any results.

In order to be able to query the embedded "visits" documents, you must change your document structure and make "visits" into an array or embedded documents, like so:

> db.test2.find().pretty()
{
    "_id" : ObjectId("4f16199d3563af4cb141c547"),
    "dob" : "10-01-1998",
    "name" : "Sam",
    "visits" : [
        {
            "service_date" : "12-5-2011",
            "payment" : 30,
            "chk_number" : "86786464"
        },
        {
            "service_date" : "12-15-2011",
            "payment" : 35,
            "chk_number" : "45643461234"
        },
        {
            "service_date" : "12-25-2011",
            "payment" : 20,
            "chk_number" : "4569821234"
        }
    ]
}

Now you can query all of the embedded documents in "visits":

> db.test2.find({"visits.payment":{$lt:35}})

For more information, please refer to the Mongo documentation on dot notation:

http://www.mongodb.org/display/DOCS/Dot+Notation+%28Reaching+into+Objects%29

Now on to the second part of your question: it is not possible to return only a conditional sub-set of embedded documents.

With either document format, it is not possible to return a document containing ONLY the sub-documents that match the query. If one of the sub-documents matches the query , then the entire document matches the query, and it will be returned.

As per the Mongo Document "Retrieving a subset of fields"

http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields

We can return parts of embedded documents like so:

> db.test2.find({"visits.payment":{$lt:35}},{"visits.service_date":1}).pretty()
{
    "_id" : ObjectId("4f16199d3563af4cb141c547"),
    "visits" : [
        {
            "service_date" : "12-5-2011"
        },
        {
            "service_date" : "12-15-2011"
        },
        {
            "service_date" : "12-25-2011"
        }
    ]
}

But we cannot have conditional retrieval of some sub documents. The closest that we can get is the $slice operator, but this is not conditional, and you will have to first know the location of each sub-document in the array:

http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields#RetrievingaSubsetofFields-RetrievingaSubrangeofArrayElements

In order for the application to display only the embedded documents that match the query, it will have to be done programmatically.

Solution 2

You may try:

$results = $mongodb->find(array("visits.payment" => array('$lt' => 30)));

But i don't know if it will work since visits is an object. BTW judging from what you posted it could be transfered to array (or should since numerical property names tends to cause confusion)