CosmosDB将结果按值排序到数组中 [英] CosmosDB sort results by a value into an array

问题描述

我有一些类似以下的CosmosDB文档

I've some CosmosDB documents like the following

{
  "ProductId": 1,
  "Status": true,
  "Code": "123456",
  "IsRecall": false,
  "ScanLog": [
    {
      "Location": {
        "type": "Point",
        "coordinates": [
          13.5957758,
          42.7111538
        ]
      },
      "TimeStamp": 201602160957190600,
      "ScanType": 0,
      "UserId": "1004"
    },
    {
      "Location": {
        "type": "Point",
        "coordinates": [
          13.5957907,
          42.7111359
        ]
      },
      "TimeStamp": 201602161246336640,
      "ScanType": 0,
      "UserId": "1004"
    }
  ]
}

如何通过TimeStamp属性对查询结果进行排序?我尝试使用此查询

How can I order the query results by the TimeStamp property? I've tried using this query

SELECT c.Code, b.TimeStamp FROM c JOIN b IN c.ScanLog ORDER BY b.TimeStamp

但我收到此错误

Order-by over correlated collections is not supported.

正确的方法是什么?

推荐答案

具有ORDER BY的JOIN.

JOINs with ORDER BY are currently not supported.

但是，这是一个可以完成此操作的用户定义函数(UDF):

However, here is a user defined function (UDF) that will do the trick:

function sortScanLog (scanLog) { 
  function compareTimeStamps(a, b) {
    return a.TimeStamp - b.TimeStamp;
  }
  return scanLog.sort(compareTimeStamps);
}

与这样的查询一起使用:

You use with a query like this:

SELECT c.ProductId, udf.sortScanLog(c.ScanLog) as ScanLog FROM c

如果您想要相反的排序顺序，只需将a和b交换即可.因此，compareTimeStamps内部函数的签名为:

If you want the opposite sort order, simply swap the a and b. So, the signature of the compareTimeStamps inner function would be:

function compareTimeStamps(b, a)

或者，您可以在返回结果后对客户端进行排序.

Alternatively, you can sort client-side after the results are returned.

这篇关于CosmosDB将结果按值排序到数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！