查询返回的结果超出预期 [英] Query returns more than expected results
问题描述
与我同在,这不是我的问题.只是想让某人理解.
Bear with me, this is not really my question. Just trying to get someone to understand.
作者注:
可能的重复问题解决方案允许$ elemMatch进行约束,因为> 所有元素都是一个数组.这有点不同.
The possible duplicate question solution allows $elemMatch to constrain because >all of the elements are an array. This is a little different.
因此,在已接受的答案中,已经提出了重点.此行为很好 记录,而您不应将'apples'与'orange's比较" .这些字段是 不同的类型,尽管有一种解决方法,但针对实际情况的最佳解决方案 世界是不要这样做.
So, in the accepted answer the main point has been brought up. This behavior is well documented and you should not "compare 'apples'` with 'oranges'". The fields are of different types, and while there is a workaround for this, the best solution for the real world is don't do this.
祝您阅读愉快:)
我有一个要搜索的文档集合,该集合包含以下内容:
I have a collection of documents I am trying to search, the collection contains the following:
{ "_id" : ObjectId("52faa8a695fa10cc7d2b7908"), "x" : 1 }
{ "_id" : ObjectId("52faa8ab95fa10cc7d2b7909"), "x" : 5 }
{ "_id" : ObjectId("52faa8ad95fa10cc7d2b790a"), "x" : 15 }
{ "_id" : ObjectId("52faa8b095fa10cc7d2b790b"), "x" : 25 }
{ "_id" : ObjectId("52faa8b795fa10cc7d2b790c"), "x" : [ 5, 25 ] }
所以我想找到 x 介于10
和20
值之间的结果.所以这对我来说似乎很合理:
So I want to find the results where x falls between the values of 10
and 20
. So this is the query that seemed logical to me:
db.collection.find({ x: {$gt: 10, $lt: 20} })
但是问题是这会在结果中返回两个文档:
But the problem is this returns two documents in the result:
{ "_id" : ObjectId("52faa8ad95fa10cc7d2b790a"), "x" : 15 }
{ "_id" : ObjectId("52faa8b795fa10cc7d2b790c"), "x" : [ 5, 25 ] }
我不希望看到第二个结果,因为所有值都不在10
和20
之间.
有人可以解释为什么我没有得到我期望的结果吗?我认为 {"x":15} 应该是返回的唯一文档.
I am not expecting to see the second result as none of the values are between 10
and 20
.
Can someone explain why I do not get the result I expect? I think { "x": 15 } should be the only document returned.
此外,我如何得到期望的结果?
So furthermore, how can I get what I expect?
推荐答案
This behaviour is expected and explained in mongo documentation here.
查询包含数组的字段
如果字段包含数组并且您的查询具有多个条件 运算符,如果单个数组中的任何一个,则整个字段将匹配 元素满足条件或 数组元素的组合 符合条件.
If a field contains an array and your query has multiple conditional operators, the field as a whole will match if either a single array element meets the conditions or a combination of array elements meet the conditions.
Mongo似乎愿意扮演自鸣得意"的角色,当数组元素的组合独立地匹配所有条件时,会返回结果.
Mongo seems to be willing to play "smug", by giving back results when a combination of array elements match all conditions independently.
在我们的示例中,5匹配$ lt:20条件,而25匹配$ gt:10条件.所以,这是一场比赛.
In our example, 5 matches the $lt:20 condition and 25 matches the $gt:10 condition. So, it's a match.
以下两项都将返回[5,25]结果:
Both of the following will return the [5,25] result:
db.collection.find({ x: {$gt: 10, $lt: 20} })
db.collection.find({ $and : [{x: {$gt: 10}},{x:{ $lt: 20}} ] })
如果这是用户预期的行为,则意见可能会有所不同.但这当然是有据可查的,应该是可以预期的.
If this is user expected behaviour, opinions can vary. But it certainly is documented, and should be expected.
编辑,以供尼尔对原始答案进行虐待狂但具有高度教育意义的编辑,以寻求解决方案:
Edit, for Neil's sadistic yet highly educational edit to original answer, asking for a solution:
使用 $ elemMatch 可以使严格"元素仅数组进行比较.
Use of the $elemMatch can make "stricter" element comparisons for arrays only.
db.collection.find({ x: { $elemMatch:{ $gt:10, $lt:20 } } })
注意:这将同时匹配x:[11,12] 和 x:[11,25]
Note: this will match both x:[11,12] and x:[11,25]
我相信,当需要这样的查询时,需要对两个查询进行组合,然后对结果进行合并.以下是一个查询,该查询返回x为非数组的文档的正确结果:
I believe when a query like this is needed, a combination on two queries is required, and the results combined. Below is a query that returns correct results for documents with x being not an array:
db.collection.find( { $where : "!Array.isArray(this.x)", x: {$gt: 10, $lt: 20} } )
但是在这种情况下,最好的方法是将x的类型更改为始终成为一个数组,即使它仅包含一个元素也是如此.然后,只需要$ elemMatch查询即可获得具有预期行为的正确结果.
But the best approach in this case is to change the type of x to always be an array, even when it only contains one element. Then, only the $elemMatch query is required to get correct results, with expected behaviour.
这篇关于查询返回的结果超出预期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!