MongoDB finds value in list of lists

Question

I use MongoDB to search for items containing a list of lists, where at least one item in the list matches the search parameter.

This is an example of the structure that I now have.

{ "Item 1":{ "data":[ ["green", 1] ] }, "Item 2":{ "data":[ ["blue", 9], ["green", 1] ] } } 

I want to search for all items that have a value of "green" in the data list.

I currently have this:

 my_data.find({'data': {'$in': ['green']}}) 

however, the results are not returned.

Answer 1

You need a “direct path” to any requested elements, and “data” is not the “top level” of the path you need to look for here, but rather “Item 1” or “Item 2” within the document and “data” is a subelement of this.

The main case is to use "dot notation" , where the root elements are "known", at least possibly exist, and also note that the $in operator is not needed to match the value inside the array, since MongoDB does not care. But instead, you need a nested $elemMatch :

 db.my_data.find({ "$or": [ { "Item 1.data": { "$elemMatch": { "$elemMatch": { "$eq": "green" } } }}, { "Item 2.data": { "$elemMatch": { "$elemMatch": { "$eq": "green" } } }} ] }) 

There are no "wildcards" to match part of the previous path, so you must specify the full path in $or to search for every possible path in the document.

If writing out all the possible prefix keys is impractical, then your only other approach is using a $where JavaScript evaluation to determine the logic to match:

 db.my_data.find(function() { var doc = this; delete doc._id; var matched = false; for ( k in doc ) { matched = doc[k].data.some(function(el) { return el.some(function(inner) { return inner === "green"; }); }); if (matched) break; } return matched; }) 

Or some variations of this logic for actually testing possible elements with data arrays to contain your green value. This is not a great solution, since $where requires the execution of each document to evaluate the state and, therefore, cannot use the index to filter the results. This means scanning the entire collection and basically the slowest approach.

It also simply displays the "shell shortcut" for writing, because otherwise the contents of function() simply represented as a "string" in the $where argument for pymongo or other language drivers, but the main context is that it has JavaScript code that evaluates to server.

Better yet, change the document so that there is always a consistent path to the item you want to request. For instance:

 { "items": [ { "name": "Item 1", "data": [["green", 1]] }, { "name": "Item 2", "data": [["blue", 9], ["green", 1]] } ] } 

Then you can simply ask this simple query:

  db.my_data.find({ "items.data": { "$elemMatch": { "$elemMatch": { "$eq": "green" } } } }) 

Since the "path" is always "items.data" , this allows one condition to test all elements only for this path