CODEWITHSUNDEEP
phpmongodbmongodb-query
danijoo
danijoo

Reputation: 2843

Querying for key in array

My document structure looks like this:

{
  "_id": ObjectId("123781236712"),
  "statistic": {
    "1": {
        "key1": "value1",
        "key2": "value2",
        (...)
    },
    "5": {
        "key1": "value1",
        "key2": "value2",
        (...)
    }

  }

}

I'm now trying to compose a find that gives me all documents that contains a statistic.5, no matter whats the content of "5".

So far, I tried without success:

db.statistics.find({"statistic": {$elemMatch: {$in:["5"]}}})
db.statistics.find({"statistic": {$elemMatch: "5"}})

thanks in advance!

Upvotes: 1

Views: 57

Answers (3)

Christian P
Christian P

Reputation: 12240

I'm now trying to compose a find that gives me all documents that contains a statistic.5, no matter whats the content of "5".

For this exact question (with your current document structure), the query you posted will work perfectly:

db.statistics.find({"statistic.5":{$exists:true}});

Your query basically looks if the key exists in the document and this is exactly what you asked in your question.

However, your current document structure isn't very practical for some queries (that's what Neil is suggesting in his answer) and there is an alternative way to organize your document structure that's more flexible and easier to query using MongoDB.

I'm going to suggest a slightly different structure than Neil's:

{
    "_id": ObjectId("..."),
    "statistic": [
        { "key1": "value1", "key2" : "value2" },
        { "key1": "value3", "key2" : "value4" },
        { "key1": "value5", "key2" : "value6" }
        /* etc ... */
    ]
}

Creating an array of subdocuments (instead of creating an object with hashed key-value pairs) will enable you to do the following queries on your document.

This will be the equivalent to your query (and what you're looking for):

db.coll.find({"statistic.5" : { $exists : 1}});

You can also check the size of the array (if the array contains exactly X items):

db.coll.find({"statistic" : { $size : 5}});

And you can also search if any of the subdocuments contains a key with a specific value (which is something that your current structure doesn't support):

db.coll.find({"statistic.key1" : "value3");

Upvotes: 0

Neil Lunn
Neil Lunn

Reputation: 151122

So pretty much as you were just told, you cannot use array operators on things that are not an array. Also "associative arrays" or "hashes" or "sub-documents" in MongoDB parlance are bad news to try and query.

Consider that you want to query for something that has "key3" equal to "value9". In your current format you would have to do:

db.collection.find({
   "$or": [
       { "statistic.1.key3": "value9" },
       { "statistic.5.key3": "value9" },
       ... // and so on for every possible key
   ]
})

If you have an array like this:

{
    "_id": "whatever",
    "statistic": [
        { "index": 1, "key1": "value1" },
        { "index": 1, "key1": "value2" },
        { "index": 5, "key1": "value1" },
        { "index": 5, "key3": "value9" }
    ]
}

Then all you need to do to query is:

db.collection.find({
    "statistic.key3": "value9"
})

Simple.

Upvotes: 0

danijoo
danijoo

Reputation: 2843

The correct solution is:

db.statistics.find({"statistic.5":{$exists:true}});

Upvotes: 1

Related Questions