Find Documents in MongoDB whose with an array field is a subset of a query array

Question

Suppose I have a insert a set of documents each with an array field. I would like to find all documents such that their array field is a subset of a query array. For example, if I have the following documents,

collection.insert([
  {
     'name': 'one',
     'array': ['a', 'b', 'c']
  },
  {
     'name': 'two',
     'array': ['b', 'c', 'd']
  },
  {
     'name': 'three',
     'array': ['b', 'c']
  }
])

and I query collection.find({'array': {'$superset': ['a', 'b', 'c']}), I would expect to see documents one and three as ['a', 'b', 'c'] and ['b', 'c'] are both subsets of ['a', 'b', 'c']. In other words, I'd like to do the inverse of Mongo's $all query, which selects all documents such that the query array is a subset of the document's array field. Is this possible? and if so, how?

You'd need to do some aggregation IMHO.

Nuk Nuk San
– Nuk Nuk San

2013-04-19 16:45:54 +00:00
Commented Apr 19, 2013 at 16:45 — Nuk Nuk San
– Nuk Nuk San, Commented Apr 19, 2013 at 16:45

diyism · Accepted Answer · 2014-03-27 03:10:44Z

11

In MongoDb, for array field:

"$in:[...]" means "intersection" or "any element in",
"$all:[...]" means "subset" or "contain",
"$elemMatch:{...}" means "any element match"
"$not:{$elemMatch:{$nin:[...]}}" means "superset" or "in"

answered Mar 27, 2014 at 3:10

diyism

13.1k5 gold badges49 silver badges47 bronze badges

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

Comments

Asya Kamsky · Accepted Answer · 2015-06-17 21:26:04Z

10

There is a simple way to do this with aggregation framework or with a find query.

Find query is simple, but you have to use $elemMatch operator:

> db.collection.find({array:{$not:{$elemMatch:{$nin:['a','b','c']}}}}, {_id:0,name:1})

Note that this indicates that we want to not match an array which has an element which is (at the same time) not equal to 'a', 'b' or 'c'. I added a projection which only returns the name field of the resultant document which is optional.

edited Jun 17, 2015 at 21:26

answered Apr 19, 2013 at 19:04

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

Comments

JohnnyHK · Accepted Answer · 2015-06-16 17:55:05Z

4

To do this within the context of aggregation, you can use $setIsSubset:

db.collection.aggregate([
    // Project the original doc and a new field that indicates if array
    // is a subset of ['a', 'b', 'c']
    {$project: {
        doc: '$$ROOT',
        isSubset: {$setIsSubset: ['$array', ['a', 'b', 'c']]}
    }},
    // Filter on isSubset
    {$match: {isSubset: true}},
    // Project just the original docs
    {$project: {_id: 0, doc: 1}}
])

Note that $setIsSubset was added in MongoDB 2.6.

answered Jun 16, 2015 at 17:55

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

1 Comment

Asya Kamsky Over a year ago

nice one. I removed the old inefficient aggregation from my answer as this is much more elegant and it's now been available for a while.

Collectives™ on Stack Overflow

Find Documents in MongoDB whose with an array field is a subset of a query array

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related