I have a collection like this

{
    "productId" : ObjectId("someId-1"),
    "value" : "Nike",
    "key" : "Brand",
},

{
    "productId" : ObjectId("someId-1"),
    "value" : "Blue",
    "key" : "Color",
},

{
    "productId" : ObjectId("someId-2"),
    "value" : "Nike",
    "key" : "Brand",
},

{
    "productId" : ObjectId("someId-2"),
    "value" : "Red",
    "key" : "Color",
},

and I want to make a query to get all product Ids that share these aspects

[
  {
    key: "Brand",
    value: "Nike",
  },
  {
    key: "Color",
    value: "Blue",
  },
]

And this should return productId = someId-1

Note that I want to make use of indexes created on all three fields(productId, key, value)

CodePudding user response：

You should use $lookup

db.collection.aggregate([
  { "$match": { "key": 'Brand', 'value':'Nike' }},
  { "$lookup": {
    "from": "collection",
    "localField": "productId",
    "foreignField": "productId",
    "as": "ids"
  }}
])

It will do a simple join in the same collection.

Refer

CodePudding user response：

I reached some point that may be the best way to handle this issue

db.collection.aggregate([
    {
        $match: {
            $or: [
                { key: 'Model', value: 'GV Special' },
                {
                    key: 'Brand',
                    value: 'Puma'
                },
                {
                    key: 'Color',
                    value: 'Multicolor'
                }]
        }
    },
    {
        $group: {
            _id: "$productId",
            products: { $push: "$productId" }
        }
    },
    {
        $match: {
            products: { $size: 3 }
        }
    }
])

But this the last match $size should be the number of aspects you want to match from the $or operator.

So, if we wanted only 2 conditions from $or to be applied we will change the $size to be 2.