Query for array of nested arrays

Working with Data
1

Hello,

I’m struggling with querying an array of nested arrays. I need a query that that will find all projects where at least one of skillsetCombinations’s has all skillsets in my searched combination, see playground Mongo playground

Example:
find skillsetCombination [A,B,D] => result project1, project3
find skillsetCombination [A,B] => result project3
find skillsetCombination [A,B,D,E] => result project1, project2, project3

Thanks, for any help
Regards,
Ivan

2

Hello @Ivan_Vlcek, Welcome to the MongoDB community forum,

I can see the input and expected result but you have to explain it so others can understand why it should be the result.

I am confused by your first query, all skills “A”, “B” and “D” are available in all 3 documents then why just result project1, project3?

3

Hi, basically, I’m looking for project where project’s nested array is subset of my array (or equal to my array).

Example explanation:
find skillsetCombination [A,B,D] =>
project1: [ABD] is subset of [ABD] - should be returned
project3: [AB] is subset of [ABD]) - should be returned
project2: none of [XYZ],[ABDE],[UVW] are subsets of [ABD] so this project shouldn’t be returned

4

Any idea how do to it? I have working query with aggregation using NIN operator on inverted items. See, Mongo playground if you are looking for all projects with ABD I do NIN on all other letters.

5

I was not able to come up with the exact combination but I have a feeling that a $reduce on a $map that uses $setIsSubset could be the key to the solution.

1 Like
6

As @steevej said, $setIsSubset seemed to work, I had a brief play this morning with an aggregate query:

db.projects.aggregate([
  {
    $addFields: {
      mapVal: {
        $map: {
          input: "$skillsetCombinations",
          as: "skillsetCombinations",
          in: {
            $setIsSubset: [
              "$$skillsetCombinations",
              [
                "A",
                "B"
              ]
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      mapValResult: {
        $reduce: {
          input: "$mapVal",
          initialValue: false,
          in: {
            $or: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      "mapValResult": true
    }
  },
  {
    $project: {
      "mapVal": 0,
      "mapValResult": 0
    }
  }
])

I’ve not tested performance etc, but the steps are:

  • Use a map to create a new array for each element to show if an element of the array is a subset
  • Use a reduce to combine ($or) then values to see if any array elements resulted in a possible match
  • Filter out items where one array element matches
  • Project out calculation fields
1 Like