How to search a string in inner array using mongodb?

Question

How to search value in multidimensional array, for example I want to search example keyword in the following data in mongodb I used to fetch all data from command

>db.info.find()

{
    "_id" : ObjectId("4f74737cc3a51043d26f4b90"),
    "id" : "12345",
    "info" : [
            {
                    "sno" : 1,
                    "name" : "ABC",
                    "email" : "abc@example.com"
            },
            {
                    "sno" : 2,
                    "name" : "XYZ",
                    "email" : "xyz@example.com"
            },
            {
                    "sno" : 3,
                    "name" : "XYZ",
                    "email" : "xyz@demo.com"
            },
            {
                    "sno" : 4,
                    "name" : "ABC",
                    "email" : "abc@demo.com"
            },
            {
                    "sno" : 5,
                    "name" : "Rohan",
                    "email" : "rohan@example.com"
            }
    ]
}

Now, to find data having example I used command

>db.info.find({"info.email":"example"}) and it gives

{
    "_id" : ObjectId("4f74737cc3a51043d26f4b90"),
    "id" : "12345",
    "info" : [
            {
                    "sno" : 1,
                    "name" : "ABC",
                    "email" : "abc@example.com"
            },
            {
                    "sno" : 2,
                    "name" : "XYZ",
                    "email" : "xyz@example.com"
            },
            {
                    "sno" : 3,
                    "name" : "XYZ",
                    "email" : "xyz@demo.com"
            },
            {
                    "sno" : 4,
                    "name" : "ABC",
                    "email" : "abc@demo.com"
            },
            {
                    "sno" : 5,
                    "name" : "Rohan",
                    "email" : "rohan@example.com"
            }
    ]
}

But I want only 3 out of 5 sub rows like

{
    "_id" : ObjectId("4f74737cc3a51043d26f4b90"),
    "id" : "12345",
    "info" : [
            {
                    "sno" : 1,
                    "name" : "ABC",
                    "email" : "abc@example.com"
            },
            {
                    "sno" : 2,
                    "name" : "XYZ",
                    "email" : "xyz@example.com"
            },
            {
                    "sno" : 5,
                    "name" : "Rohan",
                    "email" : "rohan@example.com"
            }
    ]
}

Answer 1

Did you try $ (projection)?

db.info.find({"info.email":"example"}, {"info.email.$":1})

document

Answer 2

I tried Map Reduce Function and it works on this type of problems the code is something like that:

Write a map function

  map=function () 
  {
      filter = [];
      this.info.forEach(function (s) {if (/example/.test(s.email)) {filter.push(s);}});
      emit(this._id, {info:filter});
  }

Write a reduce function

  reduce=function(key, values) { return values;}

MapReduce Function

  res=db.info.mapReduce(map,reduce,{out:{inline:1}})

And The Output look likes:

"results" : [
    {
            "_id" : ObjectId("4f9a2de0ea4a65c3ab85a9d3"),
            "value" : {
                    "info" : [
                            {
                                    "sno" : 1,
                                    "name" : "ABC",
                                    "email" : "abc@example.com"
                            },
                            {
                                    "sno" : 2,
                                    "name" : "XYZ",
                                    "email" : "xyz@example.com"
                            },
                            {
                                    "sno" : 5,
                                    "name" : "Rohan",
                                    "email" : "rohan@example.com"
                            }
                    ]
            }
    }
    ],
    "timeMillis" : 1,
    "counts" : {
            "input" : 3,
            "emit" : 3,
            "reduce" : 0,
            "output" : 3
    },
    "ok" : 1,

Now you can find your search data from

   printjson(res.results)

Answer 3

Rohan, MongoDB always returns the whole document that you are searching on. You can't just make it return the array elements in which your keyword was found. If you want to do that, then you need to make sure all all embedded documents in the "info" field are in their own collection. And that might mean that you need to link them back to the original document in your "info" collection. Perhaps something like:

{
    "sno" : 1,
    "name" : "ABC",
    "email" : "abc@example.com"
    "info_id" : "12345",
},

Alternatively, you can of course do post-processing in PHP to obtain only the rows that you want.

Answer 4

Perhaps this is a good idea? http://php.net/manual/en/class.mongoregex.php