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?

BatScream · Accepted Answer · 2014-12-12 19:42:27Z

2

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

edited Dec 12, 2014 at 19:42

answered Dec 12, 2014 at 19:20

BatScream

19.7k4 gold badges54 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sede · Accepted Answer · 2014-12-12 15:54:37Z

1

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.

answered Dec 12, 2014 at 15:54

Sede

61.5k20 gold badges158 silver badges162 bronze badges

1 Comment

Serhat Ozgel Over a year ago

This would take up too much space. Instead of this, I could always store the elements in the arrays in ascending order but I am looking for a solution for my current collection if there is one.

Max Noel · Accepted Answer · 2014-12-12 18:09:19Z

1

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.

edited Dec 12, 2014 at 18:09

answered Dec 12, 2014 at 15:38

Max Noel

8,9301 gold badge29 silver badges35 bronze badges

3 Comments

Serhat Ozgel Over a year ago

This only works when I put the numbers in the correct order. db.mytest.find({f: {$in: [[2, 1, 3]]}}) does not work.

Serhat Ozgel Over a year ago

I am not looking for documents where f contains [1,2,3]. I am looking for documents, where one of the arrays in f contains 1, 2 and 3, in any order. That's why this query does not work.

Serhat Ozgel Over a year ago

@Disposer May you please check again? My mongo version is 2.6.4 and it does not match document { "f" : [ [ 1, 2, 3 ], [ 4, 5, 6 ]]} . I also do not see how it may possibly work since $in tries to exactly match at least one of the fields.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related