问题描述
我想了解如何在内部具有数学比较器的DocumentDB上创建查询 where子句.
I would like to understand how to create query where clauses on DocumentDB with mathematical comparator inside.
例如,我使用此演示程序来了解如何进行大于"比较:表达式AND food.version > 0
看起来效果很好.
For example, I used this demonstrator to understand how to make a "greater than" comparaison : expression AND food.version > 0
seems to work very well.
以下是我尝试使用portal.azure.com documentdb查询资源管理器和结果时的内容.我不明白为什么在某些情况下会出现错误(QUERY3),以及(可选)如何在portal.azure.com上获取错误详细信息?!
Here is under what I tryed onto portal.azure.com documentdb query explorer and the results. I don't understand why I got an error in some cases(QUERY3), and (in option) how to get error details on portal.azure.com ?!
已测试:
>>> QUERY1 >>
SELECT d.id,
d.name,
d.lastUpdateTime
FROM d
>>> RESULT1 >>
[
{
"id": "558d6007b909e8dfb2286e7b",
"name": "cSimpleSIMS_ici",
"lastUpdateTime": 1435589982672
},
{
"id": "558d6009b909e8df18296e7b",
"name": "didier",
"lastUpdateTime": 1435330811285
},
{
"id": "558d600ab909e8df28296e7b",
"name": "cDoubleSIMD_ici",
"lastUpdateTime": 1435331176750
},
{
"id": "558d600bb909e8df55296e7b",
"name": "george",
"lastUpdateTime": 1435330813519
}
(...)
]
>>> QUERY2 >>
SELECT d.id,
d.name,
d.lastUpdateTime
FROM d
WHERE (d.name='george')
>>> RESULT2 >>
[
{
"id": "558d600bb909e8df55296e7b",
"name": "george",
"lastUpdateTime": 1435330813519
}
]
>>> QUERY3 >>
SELECT d.id,
d.name,
d.lastUpdateTime
FROM d
WHERE (d.lastUpdateTime > 14)
>>> RESULT3 IN ERROR!
>>> QUERY4 >>
SELECT d.id,
d.name,
d.lastUpdateTime
FROM d
WHERE (d.name='george' AND d.lastUpdateTime > 14)
>>> RESULT4 >>
[
{
"id": "558d600bb909e8df55296e7b",
"name": "george",
"lastUpdateTime": 1435330813519
}
]
>>> QUERY5 >>
SELECT d.id,
d.name,
d.lastUpdateTime
FROM d
WHERE (d.name='george' AND d.lastUpdateTime > 1435330813519)
>>> RESULT5 >>
[]
推荐答案
这是要点...
如今,DocumentDB中的所有JSON属性都由哈希索引自动索引;这意味着使用相等运算符(例如WHERE d.name= "george"
)的查询非常快.
Today, all JSON properties in DocumentDB get automatically indexed by a Hash index; which means queries with equality operators (e.g. WHERE d.name= "george"
) are extremely fast.
另一方面,范围查询(例如WHERE d.lastUpdateTime > 14
)需要范围索引才能有效运行.如果没有范围索引,范围查询将需要对所有文档进行扫描(如果请求将标头x-ms-documentdb-query-enable-scan
传递给我们,则允许这样做).
On the other hand, range queries (e.g. WHERE d.lastUpdateTime > 14
) require a range index to operate efficiently. Without a range index, the range query will require a scan across all documents (which we allow if the header, x-ms-documentdb-query-enable-scan
, is passed in by the request).
您发出的同时具有相等和范围过滤器(例如WHERE d.name='george' AND d.lastUpdateTime > 14
)的查询成功了,因为相等过滤器极大地缩小了要扫描的文档范围.
The queries you issued that had both a equality and range filter (e.g. WHERE d.name='george' AND d.lastUpdateTime > 14
) succeeded, because the equality filter greatly narrowed down the set of documents to scan through.
TL; DR:您可以在此处执行两项操作来消除错误:
TL;DR: There are two things you can do here to get rid of the error:
-
创建自定义索引策略以为数字类型添加范围索引.可以在此处找到索引策略的文档.
Create a custom index policy to add a range index for numeric types. The documentation for indexing policies can be found here.
以编程方式发出查询(而不是通过Azure门户)以设置x-ms-documentdb-query-enable-scan
标头以允许对范围查询进行扫描.
Issue your query programmatically (not through the Azure Portal) to set the x-ms-documentdb-query-enable-scan
header to allow scans on range queries.
P.S.我将努力为您改进Azure门户.
现在... Azure门户中似乎有一些问题,我将为您解决这些问题.
Now... there appear to be a few issues in the Azure Portal - which I will push to get fixed for you.
错误:异常消息被截断了
使用Azure门户时,看起来异常消息的有意义部分被截断了-这不是bueno.应该显示的是:
Looks like the meaningful part of the exception message gets truncated out when using the Azure Portal - which is no bueno. What SHOULD have been displayed is:
Microsoft.Azure.Documents.DocumentClientException: Message: {"Errors":["An invalid query has been specified with filters against path(s) that are not range-indexed. Consider adding allow scan header in the request."]}
缺少功能:在查询浏览器中启用扫描
Azure门户的查询资源管理器中当前未提供设置x-ms-documentdb-query-enable-scan
标头的功能.我们将为此添加一个复选框或其他内容.
There ability to set the x-ms-documentdb-query-enable-scan
header is currently not exposed in the Azure Portal's query explorer. We will add a checkbox or something for this.
这篇关于DocumentDb的"where"位置带有数学表达式的子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!