I have 4 products. I want to know the count of product-4 for users who has product-1 or product-2

Sample data:

[
  {
    "user_id": 1,
    "product_type": "product-1"
  },
  {
    "user_id": 1,
    "product_type": "product-4"
  },
  {
    "user_id": 1,
    "product_type": "product-4"
  },
  {
    "user_id": 2,
    "product_type": "product-1"
  }
]

user-1 has two product-4 and one product-1 (that counts 2)

user-2 has only product-1, but no product-4 (hence that does not count)

This is how I tried

db.collection.aggregate([
  {
    $match: {
      product_type: {
        $in: [
          "product-1​",
          "product-2",
        ],
      },
    },
  },
  {
    $group: {
      _id: "$user_id",
    },
  },
  {
    $match: {
      user_id: { $in: "$_id"}, // I want to use $group's result in here
      product_type: "product-4",
    },
  }
]);

Expected results are:

[
  {
    "_id": 1,
    "count": 2
  },
  {
    "_id": 2,
    "count": 0
  }
]

Note: I dont have a backend, I have to this using mongodb only.

CodePudding user response：

Does this answer your question?

db.collection.aggregate([
  {$group: {_id: "$user_id", data: {$push: "$product_type"}}},
  {$match: {$expr: {$or: [
          {$in: ["product-1", "$data"]},
          {$in: ["product-2", "$data"]}
        ]}}},
  {$project: {
      count: {
        $size: {
          $filter: {
            input: "$data",
            cond: {$eq: ["$$this", "product-4"]}
          }
        }
      }
  }}
])

See how it works on the playground example