Zulway Zulway - 2 months ago 8
Node.js Question

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

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

Answer

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" ] }