mysql - SQL JSON 数组排序

Question

在数据库中记录:

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 PATH”方法，我将把它作为练习留给读者。

如果你想应用到一个完整的sql表，使用CROSS APPLY如下:

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