Matching all elements in an array in an array of arrays with $all in MongoDB

Question

I have a document which consists of documents like these:

{ "f" : [ [ 1, 2, 3], [4, 5, 6] ] } // should match because of [1, 2, 3]
{ "f" : [ [ 2, 1, 3], [4, 5, 6] ] } // should match because of [2, 1, 3]
{ "f" : [ [ 1, 2, 4], [4, 5, 6] ] } // should NOT match

In this collection, I want to match documents, which has an array containing 1, 2 and 3 in one of the arrays of the "f" field.

What I've tried so far:

db.mytest.find({ f: { $elemMatch: { $all: [1,2,3] } } } )

I expect this query to work but I do not understand why it does not. I does not match any documents.

db.mytest.find({ f: { $elemMatch: { $elemMatch: { $all: [1,2,3] } } } })

This also does not work.

db.mytest.find({ f: { $all: [[1,2,3]] } })

This works but the elements have to be in exact order. I want to be able to match when the input array is 2, 1, 3 also. One possible solution must be to always store elements in ascending order and use this query.

db.mytest.find({ f: { $elemMatch: { $elemMatch: { $in: [1, 2, 3] } } } })

This works but it matches all the documents containing any one of 1, 2 or 3. I only want the documents which contain exactly 1, 2 and 3 in a single array.

What is the query I am looking for?

Answer 1

It seems that the operators perform an exact match in case of embedded arrays. Not the most pleasing or optimal solution(you need to test it with the records in your collection), but one way of doing it is via the aggregation pipeline on the server side.

• Project an extra field for each document representing the "f" value.
• unwind the actual "f" field, so that we can match a single array elements.
• match the documents where "f" contains all the values in the input array.
• project back the temporary field for the original document.

Code:

db.mytest.aggregate([
{$project:{"temp":"$f","f":1}},    // maintain a temporary variable for projection
{$unwind:"$f"},                    // f:[1,2,3] f:[4,5,6] become separate records.
{$match:{"f":{$all:[1,2,3]}}},     // use $all to match all the elements. 
{$project:{"f":"$temp"}}           // project the temporary variable.
])