时间戳

首页 > 解决方案 > 使用 SQL 在 CosmoDB 内的集合的 JSON 集合中过滤日期时间值

sql - 使用 SQL 在 CosmoDB 内的集合的 JSON 集合中过滤日期时间值

问题描述

使用 Microsoft CosmoDBs SQL 类语法。我有一组条目遵循这样的模式（为这篇文章简化了）

{"id":"123456",
 "activities": {
       "activityA": {
                "loginType": "siteA",
                "lastLogin": "2018-02-06T19:42:22.205Z"
            },
       "activityB": {
                "loginType": "siteB",
                "lastLogin": "2018-03-07T11:39:50.346Z"
            },
       "activityC": {
                "loginType": "siteC",
                "lastLogin": "2018-04-08T15:21:15.312Z"
            }
        }
}

在不知道活动条目活动列表/子集合的确切索引的情况下，如何查询以取回 Cosmo db 集合中具有与日期范围匹配的“lastLogin”的所有项目？

如果我只想搜索活动列表中的第一项，我可以使用索引 0 执行类似的操作。

SELECT * FROM c where (c.activities[0].lastLogin > '2018-01-01T00:00:00') and (c.activities[0].lastLogin <= '2019-02-15T00:00:00')

但我想搜索列表中的所有条目。如果有这样的东西会很好：

SELECT * FROM c where (c.activities[?].lastLogin > '2018-01-01T00:00:00') and (c.activities[?].lastLogin <= '2019-02-15T00:00:00')

但这不存在。

标签： sqlazurenosqlazure-cosmosdb

解决方案

The answer is that you can not iterate over a non list collection. Had the collection item been structured like this

{"id":"123456",
 "activities": [
            {   "label": "activityA",
                "loginType": "siteA",
                "lastLogin": "2018-02-06T19:42:22.205Z"
            },
            {
                "label": "activtyB",
                "loginType": "siteB",
                "lastLogin": "2018-03-07T11:39:50.346Z"
            }, 
etc...

It would be easy to crease a UDF to iterate over with something like this

UDF: filterActivityList

function(activityList,  targetDateTimeStart, targetDateTimeEnd) {
    var s, _i, _len;
    for (_i = 0, _len = activityList.length; _i < _len; _i++) {

        s = activityList[_i];

        if ((s.lastLogin >= targetDateTimeStart) && (s.lastLogin < targetDateTimeEnd)) 
        {

            return true;
       }

    }
    return false;
}

Then to query:

select * from c WHERE udf.filterActivityList(c.activities, '2018-01-01T00:00:00', '2018-02-01T00:00:00');

If I were to leave the structure as a JSON hierarchy instead of converting it to a JSON list then I would have to write another udf to accept the top level node of the hierarchy as an input parameter and have it convert the notes under it to a list, then apply the udf.filterActivityList UDF to the result. From my experience this approach is resource intensive and takes a very long time for Cosmo to process.

推荐阅读