将$ or与$ elemMatch一起使用，并在Array之外使用条件 [英] Use $or with $elemMatch and condition outside of Array

问题描述

我的基本结构是我有一个带有会话对象的User对象，该会话对象包含subjectId和每小时价格.

My basic structure is I have User object with a session object that contains a subjectId and an hourly price.

User{
    defaultHourly: Number,
    subjects{
        [
            id: String,
            hourly: Number
        ]
    }
}    

我这样使用elemMatch:

I use elemMatch like this:

query.elemMatch("subjects", {
                "id": { $in: subjects },
                "$or": [
                    { "hourly": { $eq: null } }, // this is my issue
                    {
                        "$and": [
                            { "hourly": { $ne: null } },
                            { "hourly": { $gte: price.low, $lte: price.high } }
                        ]
                    }
                ]
            });

elemMatch的第一部分确保我想要的subjectId在主题数组中.然后我要按价格过滤.如果用户在小时字段中为空，那么我想将defaultHourly与price.low和price.high进行比较.

the first part of elemMatch makes sure that the subjectId I want is in the array of subjects. Then I want to filter by price. if a User has null in the hourly field, I want to then compare the defaultHourly to price.low and price.high.

问题在于defaultHourly不是主题中的字段，而是主题的父级中的一个字段，因此我不确定如何访问它.

The issue is that defaultHourly is not a field in subjects, but is a field in the parent of subjects so I'm not sure how to access it.

我想写:

"$or": [
           { 
                "$and": [
                   { "hourly": { $eq: null } },
                   { "defaultHourly": { $gte: price.low, $lte: price.high } } //this doesnt work because default hourly is not a field.
                ]
           },
           {
               "$and": [
                   { "hourly": { $ne: null } },
                   { "hourly": { $gte: price.low, $lte: price.high } }
                ]
            }
       ]
 }); 

如何进行多级比较?

推荐答案

您将条件写在 $elemMatch ，然后放入 $or 在文档的顶层.这允许每个组"条件都适用:

You write the condition outside of the $elemMatch, and put the $or at the top level of the document. This allows each "set" of conditions to apply:

User.find({
  "$or": [
     { 
       "defaultHourly": { "$gte": price.low, "$lte": price.high },
       "subjects": {
         "$elemMatch": { 
           "id": { "$in": subjects },
           "hourly": { "$eq": null }
         }
       }
     },
     {
       "subjects": {
         "$elemMatch": {
           "id": { "$in": subjects },
           "hourly": { "$gte": price.low, "$lte": price.high }
         }
       }
     }
  ] 
})

还请注意，不需要 $ne ，因为范围"比较实际上会否定null，因为它根本不会大于"实际数值.

Also note the $ne is not needed, since the "range" comparison would actually negate null since it simply would not be "greater than" an actual numeric value.

此外，请注意，所有MongoDB查询表达式已经是AND条件，例如 $lte 在同一属性上.因此，即使不是必需的， $ne 也可以被写为:

In addition, note that ALL MongoDB query expressions are already AND conditions, such as the $gte and $lte on the same property. So even though it's not required, the $ne can also be written in as:

   "hourly": { "$gte": price.low, "$lte": price.high, "$ne": null }

不需要任何显式的 $and

Which removes any need for an explicit $and.

这篇关于将$ or与$ elemMatch一起使用，并在Array之外使用条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！