MongoDB - How to do queries on multiple attributes within an object and group the results

Question

I'm trying to learn MongoDB and I can't seem to figure out how to do this one.

Let's say I have three object like this:

         {
            "_id": 99990,
            "type" : 15,
            "attributes": [
                {
                    "id": 1,
                    "value": 115
                }
            ]
        },
        {
            "_id": 99991,
            "type" : 5,
            "attributes": [
                {
                    "id": 1,
                    "value": 120
                }
            ]
        },
        {
            "_id": 99992,
            "type" : 5,
            "attributes": [
                {
                    "id": 1,
                    "value": 120
                }
            ]
        },
        {
            "_id": 99993,
            "type" : 5,
            "attributes": [
                {
                    "id": 1,
                    "value": 150
                }
            ]
        },

How can I search for items and return only id 99993 based on the attribute "id" 1 with a value of 150 ? I've just started playing with MongoDB today so this is probably a very basic question.

To add to the challenge, how can I group the results for every item of type 5 by the attributes/id/value, and get a result like:

 "id" : 1, "value" : 150, "count" : 1
 "id" : 1, "value" : 120: "count" : 2

This is just example data, the schema is pretty complex but for the purpose of this exercise it should be sufficient.

Answer 1

Since "attributes" is an array of objects, you should use '$elemMatch' in your query.

db.collectionName.find(
   {
       "attributes" : {
           $elemMatch : {
               "id" : 1, 
               "value": 150
           }
       }
   }
);

If we are using the query @clexmond suggested, the below-mentioned sample document also will be returned, which is incorrect.

   {
        "_id": 99999,
        "type" : 5,
        "attributes": [
            {
                "id": 1,
                "value": 170
            },
            {
                "id": 2,
                "value": 150
            }
        ]
    }

The second part of the question even I also don't know. I would like to see the answer to that. Please share if you have got already.

Answer 2

For the first part, you should be able to retrieve that record by doing:

db.collectionName.find({"attributes.id": 1, "attributes.value": 150});

That will retrieve only the _id field from any object that has an element of the attributes array that has id = 1 and value = 150.

Since the second part of your question involves returning a count of matches, you should explore your options with map-reduce. You can't produce that result with a simple Mongo query.