Mongodb: find documents with array where all elements exist in query array, but document array can be smaller

Question

I have a Collection in my database where most documents have an array-field. These arrays contain exactly 2 elements. Now i want to find all documents where all of those array elements are elements of my query array.

Example Documents:

{ a:["1","2"] },
{ a:["2","3"] },
{ a:["1","3"] },
{ a:["1","4"] }

Query array:

["1","2","3"]

The query should find the first 3 documents, but not the last one, since there is no "4" in my query array.

Expected Result:

{ a:["1","2"] },
{ a:["2","3"] },
{ a:["1","3"] }

Looking forward to a helpful answer :).

Joachim Isaksson · Accepted Answer · 2014-06-18T19:32:59.427

Since the size is static, you can just check that both elements are in [1,2,3];

db.test.find(
  { $and: [ { "a.0": {$in: ["1","2","3"] } },
            { "a.1": {$in: ["1","2","3"] } } ] },
  { _id: 0, a: 1 }
)

>>> { "a" : [ "1", "2" ] }
>>> { "a" : [ "2", "3" ] }
>>> { "a" : [ "1", "3" ] }

EDIT: Doing it dynamically is a bit more hairy, I can't think of a way without the aggregation framework. Just count matches as 0 and non matches as 1, and finally remove all groups that have a sum != 0;

db.test.aggregate(
  { $unwind: "$a" },
  { $group: { _id: "$_id", 
              a: { $push: "$a" },
              fail: { $sum: {$cond: { if: { $or: [ { $eq:["$a", "1"] },
                                                   { $eq:["$a", "2"] },
                                                   { $eq:["$a", "3"] }]
                                          },
                                      then: 0,
                                      else: 1 } } } } },
  { $match: { fail: 0 } },
  { $project:{ _id: 0, a: 1 } } 
)

>>> { "a" : [ "1", "3" ] }
>>> { "a" : [ "2", "3" ] }
>>> { "a" : [ "1", "2" ] }

Very nice. This works for me and I accepted it as an answer. If someone finds out a non-static way though, that would be even better. — Zulway, Jun 18 '14 at 19:03

score 2 · Answer 2 · answered Sep 10 '16 at 09:30

I also think, that it's impossible without the aggregation framework (if elements count is dynamic). But I found out more universal way of doing that:

db.tests.aggregate({
  $redact: {
    $cond: {
      if: {$eq: [ {$setIsSubset: [ '$a', [ "1", "2", "3" ] ]}]},
      then: '$$KEEP',
      else: '$$PRUNE'
    }
  }
})

score 0 · Answer 3 · answered Jun 18 '14 at 18:25

0

I believe the answer to your problem is to use

$in

(from the docs:)

Consider the following example:

db.inventory.find( { qty: { $in: [ 5, 15 ] } } )

This query selects all documents in the inventory collection where the qty field value is either 5 or 15. Although you can express this query using the $or operator, choose the $in operator rather than the $or operator when performing equality checks on the same field.

You can also do more complex stuff using arrays. Checkout: http://docs.mongodb.org/manual/reference/operator/query/in/

answered Jun 18 '14 at 18:25

jordyyy

54
1
7

This would also find the last document containing a "4" in the array, since it matches the "1". If the document contains one element that isnt in the query array, i want it filtered out. – Zulway Jun 18 '14 at 18:44
I realized this soon enough and began to work on a solution with $and and $where, but Joachim has solved it with a working solution :) – jordyyy Jun 18 '14 at 18:59

Mongodb: find documents with array where all elements exist in query array, but document array can be smaller

3 Answers3