Conditional threshould matching in MongoDB-CodePudding

I have this mongodb query filtering contents by the content_type field and a threshold over an array of scores. This threshold is the same for all content_types but I would like to make is specific for each content_type:

{'content_type': {'$in': ['A','B','C']}, 
'$or': [{'scores.EE': {'$gt': 6.5}},{'scores.FF': {'$gt': 6.5}},{'scores.GG': {'$gt': 6.5}}]}

The pseudo code for this change would be something like this (simplified for only one score field) :

{'content_type': {'$in': ['A','B','C']}, 
'$or': [{'scores.EE': {'$gt': 8 (if content_type == 'A')} and 4 if (if content_type == 'B')} and {'$gt': 6 (if content_type == 'C')}]}

Given the following documents as input, I would like to match all of the first group and discard the second group, (simplified using only the scores.EE field):

group 1:

{ "content_type": "A",   "scores.EE" = 9 }, 
{ "content_type": "B",   "scores.EE" = 5 }, 
{ "content_type": "C",   "scores.EE" = 7 }

group 2:

{ "content_type": "A",   "scores.EE" = 7 }, 
{ "content_type": "B",   "scores.EE" = 3 }, 
{ "content_type": "C",   "scores.EE" = 5 }

Also from a performance point of view, does this kind of query use the indexes efficiently?

CodePudding user response：

Under the assumptions your final query will look a little something like this:

{
    {
        $or: [
            {
                $and: [
                    {
                        type: "A"
                    },
                    {'$or': [{'scores.EE': {'$gt': 1}},{'scores.FF': {'$gt': 2}},{'scores.GG': {'$gt': 3}}]}
                ]
            },
            {
                $and: [
                    {
                        type: "B"
                    },
                    {'$or': [{'scores.EE': {'$gt': 4}},{'scores.FF': {'$gt': 5}},{'scores.GG': {'$gt': 6}}]}
                ]
            }
            {
                $and: [
                    {
                        type: "C"
                    },
                    {'$or': [{'scores.EE': {'$gt': 6.5}},{'scores.FF': {'$gt': 6.5}},{'scores.GG': {'$gt': 6.5}}]}
                ]
            }
        ]
    }
}

Then yes it can utilize a query pretty well, you just want to build a separate index for each score.XX field.

No single index can answer this query well, the best results will probably come from a simple type index.