How to retrieve document with a subset of keys from a larger set in MongoDB?

user_14709956 0
Viewed 98

I have a database with documents that have an array of arrays of strings. Something akin to the document {name: "Test", keys: [["key1","key2"],["key3"],["key4", key5","key6"],["key7"]]}

Given a list of keys, I want to query the database for all of the documents that have a at least one key in each sub array. So, if I ask for all documents given the list of keys: "key1", "key3", "key5", "key7", "key9" the above document would be returned because all of the inner arrays have at least one key from the query.

However, given a list of keys such as: "key1", "key3", "key7", "key9" the above document would not be returned because the 3rd inner array does not include one of the keys.

I have done a lot of research and cannot find a way to do this. I do not know if this is even possible. But, all input would be greatly appreciated. Thank you!

mongodb arrays database
1 Answers

A trick is

  • We will get all the intersected values between $keys and user given values using $map. At the same time get the total count also.
  • Remove all non element arrays using $filter and get the count also
  • $match to check both counts, if both counts are same, then you can retrieve the documents, else remove the documents

And the script is

db.collection.aggregate([
  {
    $addFields: {
      dupkeys: {
        "$map": {
          input: "$keys",
          in: {
            "$setIntersection": [
              "$$this",
              ["key3","key1", "key4","key7"]
            ]
          }
        }
      },
      total: {  $size: "$keys" }
    }
  },
  {
    $addFields: {
      nonMatched: {
        $size: {
          $filter: {
            input: "$dupkeys",
            cond: {
              $ne: [ "$$this", [] ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $eq: ["$total", "$nonMatched" ]
      }
    }
  }
])

Working Mongo playground

user_79757710
Related