DocumentDb的"where"位置带有数学表达式的子句 [英] DocumentDb "where" clause with mathematical expression

问题描述

我想了解如何在内部具有数学比较器的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:

1. 创建自定义索引策略以为数字类型添加范围索引.可以在此处找到索引策略的文档.

1. 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"位置带有数学表达式的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！