Home > Back-end >  MongoDB - Query collection's array contains all element in input array
MongoDB - Query collection's array contains all element in input array

Time:05-31

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']
  },
]

CodePudding user response:

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

  1. $match - Filter the documents (as explained in the first answer for $expr).
  2. $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)

  • Related