How to search in all object keys?

Question

Given multiples documents containing a key named userId, where this key is always an object:

    {
        "_id": {
            "$oid": "22fc6b11a0ff111d598b114f"
        },
        "userId": {
            "KEY1"  : ["..."],
            "KEY2"  : ["..."],
            "KEY3"  : ["..."],
            "KEY4"  : ["..."]
        },
    }

It will always contain 4 sub-keys, where each of these is always a non-empty array of strings

How I can search over all documents looking inside each of these KEY's?

For example:

    /* MONGO document

    {
        "_id": {
            "$oid": "65fc6b08a0ffe6dd598b114f"
        },
        "userId": {
            "KEY1"  : ["1", "2", "3"],
            "KEY2"  : ["A", "Z"]
            ...
        }
    },
    {
        "_id": {
            "$oid": "22fc6b11a0ff111d598b114f"
        },
        "userId": {
            "KEY1"  : ["4", "5", "6"],
            "KEY2"  : ["Z", "X"]
            ...
        },
    }
    */

    const array = ["2", "X"];
    const users = await db.collection("users").find({ "userId": { "$in": array } }).toArray();
    console.log(users)

const array = ["2", "X"]; <- I'm trying to specify an array of strings and find all documents that contains any of these strings in any of the sub-keys arrays of the userId object

In this case, it would match both documents, because 2 exists on the first document userId.KEY1 and X exists on the second document userId.KEY2

const array = ["X"]; This would match just the second document

My attempt is not finding any match, how i could achieve this?

Answer 1

You can use $setIntersection of the input array with the $setUnion of the values of KEY's. Note however that this requires mongo to construct the Union before doing the intersection check which may not be very optimal and won't use any indexes.

db.collection.find({
  $expr: { $ne: [
    {
      $setIntersection: [
        ["2", "X"],
        { $setUnion: ["$userId.KEY1", "$userId.KEY2", "$userId.KEY3", "$userId.KEY4"] }
      ]
    },
    []]
  }
})

Mongo Plaground

And if you have many KEY's, it fits well with jQueeny's answer of constructing a string of $KEY's to use in the setUnion part.

If you did have an arbitrary number of KEYs - and not always the exact same 4 - you can also use $objectToArray as suggested in the comments:

db.collection.aggregate([
  { $set: { allKEYs: { $objectToArray: "$userId" } } },
  { $match: { "allKEYs.v": { $in: ["2", "X" ] } } },
  { $unset: "allKEYs" }
])

Mongo Playground

Also won't use any index so I'd recommend jQueeny's answer over this for your scenario.

Answer 2

To archive your goal, you need to use the MongoDB aggregation framework with $or operator and $eleMatch to search within array of objects.

const array = ["2", "X"];

const user = await db.collection("users").aggregate([
{
$match:{
$or: {
{ "userId.KEY1": { $elemMatch: { $in: array } } },
{ "userId.KEY2": { $elemMatch: { $in: array } } },
{ "userId.KEY3": { $elemMatch: { $in: array } } },
}
}
}
]).toArray();
console.log(users);

This query will match documents where any of the userId sub-keys contain at least one element that is present in the array

Answer 3

You can use a simple $or in your find() query like so:

const array = ["2", "X"];
const users = await db.collection("users").find({
  $or: [
    {
      "userId.KEY1": {
        $in: array
      }
    },
    {
      "userId.KEY2": {
        $in: array
      }
    },
    {
      "userId.KEY3": {
        $in: array
      }
    },
    {
      "userId.KEY4": {
        $in: array
      }
    }
  ]
}).toArray();

See HERE for a working example.

If you have a long list of KEYn keys for example KEY1, KEY2...KEY99 then you can construct your query object $or array using a simple for loop like so:

const array = ["2", "X"];
const orArray = [];
const numKeys = 100; // range from KEY1...KEY99
for(i=1; i < numKeys; i++){
  orArray.push(
    {
      [`"userId.KEY${i}"`]: {
        "$in": array
      }
    }
  );
}
const users = await db.collection("users").find({
  $or: orArray
}).toArray();