MongoDB - Query collection's array contains all element in input array

Question

Let's say I have those documents below:

[
  {
    array : ['a', 'b' , 'c'],
  },
  {
    array : ['b', 'd' , 'e'],
  },
  {
    array : ['d', 'e' , 'f'],
  },
]

and input array for query:

["b","d","e","f"]

Expected output:

['b', 'd' , 'e'],['d', 'e' , 'f']

Which query can I use to do that? And how to filter which element is not in the document?

Expected result:

[
  {
     array : ['b', 'd' , 'e'],
     missingElement : ['f']
  },
  {
     array : ['d', 'e' , 'f'],
     missingElement : ['b']
  },
]

Yong Shun · Accepted Answer · 2022-05-31 03:08:11Z

1

$expr - Allow to use aggregation operator.

1.1. $eq - Compare the result from 1.1.1 and 1.1.2 are equal.

1.1.1. $size - Get the size of array field.

1.1.2. $size - Get the size of array from the result 1.1.2.1.

1.1.2.1. $setIntersection - Intersect array field and input array, return the intersected value(s) in array.

db.collection.find({
  $expr: {
    $eq: [
      {
        $size: "$array"
      },
      {
        $size: {
          $setIntersection: [
            "$array",
            [
              "b",
              "d",
              "e",
              "f"
            ]
          ]
        }
      }
    ]
  }
})

Sample Mongo Playground

Updated

For Aggregation query to find missing element(s):

$match - Filter the documents (as explained in the first answer for $expr).
$project - Decorate the output documents. For missingElement field, you need $filter operator to find each value in the input array does not exist ($not and $in) in the array.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $eq: [
          {
            $size: "$array"
          },
          {
            $size: {
              $setIntersection: [
                "$array",
                [
                  "b",
                  "d",
                  "e",
                  "f"
                ]
              ]
            }
          }
        ]
      }
    }
  },
  {
    $project: {
      array: 1,
      missingElement: {
        $filter: {
          input: [
            "b",
            "d",
            "e",
            "f"
          ],
          cond: {
            $not: {
              $in: [
                "$$this",
                "$array"
              ]
            }
          }
        }
      }
    }
  }
])

Sample Mongo Playground (Aggregation query)

edited May 31, 2022 at 3:08

answered May 31, 2022 at 2:45

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

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

2 Comments

vy.pham Over a year ago

It's worked. Thank you so much. Can I use aggregation to filter which element is not in the document? I edited my question. Can you take a look? Thanks!

Yong Shun Over a year ago

Hi, ya, aggregation query will be easier to achieve. You may refer to this demo.

Collectives™ on Stack Overflow

MongoDB - Query collection's array contains all element in input array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related