SQL JSON数组排序

Question

db中的记录：

id     info
0     [{"name":"a", "time":"2017-9-25 17:20:21"},{"name":"b", "time":"2017-9-25 23:23:41"},{"name":"c", "time":"2017-9-25 12:56:78"}]

我的目标是对time上的JSON数组列info基础进行排序，例如：

id     info
0     [{"name":"c", "time":"2017-9-25 12:56:78"},{"name":"a", "time":"2017-9-25 17:20:21"},{"name":"b", "time":"2017-9-25 23:23:41"},]

我使用SparkSQL，没有线索

Answer 1

您可以通过将JSON数组转换为SQL结果集，提取排序列，最后将其转换回JSON数组：

来做到这一点：

DECLARE @json NVARCHAR(MAX);
SET @json = '[
    {"name":"a", "time":"2017-09-25 17:20:21"},
    {"name":"b", "time":"2017-09-25 23:23:41"},
    {"name":"c", "time":"2017-09-25 12:56:59"}
    ]';
WITH T AS (
    SELECT [Value] AS array_element
        , TRY_CAST(JSON_VALUE(Value, 'strict $.time') AS DATETIME) AS sorting
    FROM OPENJSON(@json, 'strict $')
)
SELECT STRING_AGG(T.array_element, ',') WITHIN GROUP (ORDER BY sorting)
FROM T

注意：

• 由于无效的月份和秒，我稍微更改了样本数据。
• String_agg（）函数仅可从SQL 2017/Azure SQL数据库获得。对于较旧的版本，请使用经典的" for xml路径"方法，我将作为练习留给读者。

如果要将其应用于完整的SQL表，请使用Cross应用如下：

DECLARE @json NVARCHAR(MAX);
SET @json = '[
    {"name":"a", "time":"2017-09-25 17:20:21"},
    {"name":"b", "time":"2017-09-25 23:23:41"},
    {"name":"c", "time":"2017-9-25 12:56:59"}
    ]';
WITH dat AS (
    SELECT * FROM (VALUES (1,@json), (2,@json)) AS T(id, info)
)
, T AS (
    SELECT id, [Value] AS array_element
            , TRY_CAST(JSON_VALUE(Value, 'strict $.time') AS DATETIME) AS sorting
    FROM dat
    CROSS APPLY OPENJSON(info, 'strict $')
)
SELECT id
    , STRING_AGG(T.array_element, ',') WITHIN GROUP (ORDER BY sorting) AS info
FROM T
GROUP BY id

Answer 2

我建议

替代数据库存储系统

我不建议以这种方式存储数据。它只是使您的数据现在越来越易于访问和延展。如果您这样存储数据：

id     name    time
0      a       2017-9-25 17:20:21
0      b       2017-9-25 23:23:41
0      c       2017-9-25 12:56:71
1      ...     ...

然后，您可以在选择查询结束时使用ORDER BY方法在时间顺序上选择数据。例如：

SELECT name, time FROM table_name where id=0 ORDER BY time asc;

如果您在此表中有更多的列，则可能需要另一个表来有效地存储信息，但是外键和在这些类型的表之间加入的性能优势将超过所有数据一张表作为不便的JSON数组。