I have collection that looks like EAV (or some key/value):

{domain_id: 1, key: "A", value: 1}
{domain_id: 1, key: "B", value: 2}
{domain_id: 1, key: "C", value: 3}

{domain_id: 2, key: "A", value: 5}
{domain_id: 2, key: "B", value: 2}
{domain_id: 2, key: "C", value: 3}

{domain_id: 3, key: "C", value: 3}

I need to find all domain_id what have key = "A" AND value = 1 and also have key = "C" AND value = 3 (intersection, not OR condition) so supposed result will be:

{domain_id: 1}

CodePudding user response：

1. $group - Group By domain_id and $push document in data field.
2. $match - Use $and for multiple comparisons, $elemMatch to match field value in array element.
3. $project - Select domain_id.

db.collection.aggregate([
  {
    $group: {
      _id: "$domain_id",
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $match: {
      $and: [
        {
          "data": {
            $elemMatch: {
              "key": "A",
              "value": 1
            }
          }
        },
        {
          "data": {
            $elemMatch: {
              "key": "C",
              "value": 3
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      domain_id: "$_id"
    }
  }
])

Sample Mongo Playground