CODEWITHSUNDEEP
mongodbmongodb-query
Serhat Ozgel
Serhat Ozgel

Reputation: 23766

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

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?

Upvotes: 5

Views: 904

Answers (3)

BatScream
BatScream

Reputation: 19700

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.
])

Upvotes: 2

Max Noel
Max Noel

Reputation: 8910

You're looking for documents where f contains [1, 2, 3]. Try db.mytest.find({f: {$in: [[1, 2, 3]]}}).

The easiest way to find all the possible permutations is, unfortunately, to construct them in your program (if you're using Python, itertools.permutations will do that for you) and change your $in query to [[1, 2, 3], [2, 1, 3], ...]. It sucks.

Upvotes: 1

Sede
Sede

Reputation: 61225

What about generate all permutations of your array and use in your query?? something like this:

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

Perhaps this is not the efficient way.

Upvotes: 1

Related Questions