mysql - 使用 MySQL JSON_TABLE 将具有未知键的 JSON 扩展到行

Question

我有一个 MySQL 8.0.22 JSON 列，其中包含具有事先不知道的键的对象:

'{"x": 1, "y": 2, "z": 3}'
'{"e": 4, "k": 5}'

我想使用 JSON_TABLE 将这些值扩展为包含键值对的多行:

<表类="s-表"><头>键值<正文>x1是2z3e4k5

困难当然在于 key 不是先验。我想到的最好的事情是......

SET @json_doc = '{"x": 1, "y": 2, "z": 3}';

SELECT a.seq, b.k, a.v

    FROM

    JSON_TABLE(
        @json_doc,
        "$.*"
        COLUMNS(
            seq FOR ordinality,
            v INT PATH "$"
        )
    ) AS a,

    JSON_TABLE(
        JSON_KEYS(@json_doc),
        "$[*]"
        COLUMNS(
            seq FOR ordinality,
            k CHAR(1) PATH "$"
        )
    ) AS b

    WHERE a.seq = b.seq;

这感觉很奇怪，因为它使用了两个 JSON_TABLE 调用，对值和键进行交叉连接，然后保持对齐。我想找到一个像这样的更简单的查询...

SELECT a.seq, b.k, a.v

    FROM
    
    JSON_TABLE(
        @json_doc,
        "$.*"
        COLUMNS(
            seq FOR ordinality,
            k CHAR(1) PATH "?"  -- <-- what do I put here to find each key?
            v INT PATH "$"
        )
    ) AS a,

我知道这个问题可能可以通过 CTE 或数字表和 JSON_EXTRACT 来解决。但是，如果可能的话，我想找到一些高性能和可读的东西。

Answer 1

您可以使用 ROW_NUMBER() 窗口函数来使用枚举，同时使用 JSON_KEYS() 确定键值，然后使用 提取相应的键>JSON_EXTRACT() 从我们得到的数组中

WITH k AS
(
SELECT *, 
       ROW_NUMBER() OVER(PARTITION BY `jsdata` ORDER BY value DESC) AS rn,
       JSON_KEYS(`jsdata`) AS jk
  FROM `tab` AS t
  JOIN JSON_TABLE(`jsdata`,'$.*' COLUMNS (value INT PATH '$')) j
)
SELECT JSON_UNQUOTE(JSON_EXTRACT(jk, CONCAT('$[',rn-1,']'))) AS "key", 
       value
  FROM k

或使用以下查询更直接

SELECT JSON_UNQUOTE(
       JSON_EXTRACT(JSON_KEYS(`jsdata`), 
                    CONCAT('$[',
                    ROW_NUMBER() OVER(PARTITION BY `jsdata` ORDER BY value DESC)-1,
                    ']'))
                   ) AS "key", value
  FROM `tab` AS t
  JOIN JSON_TABLE(`jsdata`,'$.*' COLUMNS (value INT PATH '$')) j

Demo