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

zi8p0yeb 于 2023-03-20 发布在 Mysql

关注(0)|答案(3)|浏览(182)

我有一个MySQL 8.0.22 JSON列，其中包含具有未知键的对象：

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

我想使用JSON_TABLE将这些值扩展到包含键值对的多行中：
| 键|价值|
| - ------|- ------|
| x|1个|
| Y型|第二章|
| z|三个|
| 电子|四个|
| k|五个|
当然，困难的是钥匙不是事先就知道的。我想出的最好的办法是...

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来解决，但是，如果可能的话，我想找到一些性能好、可读性强的东西。

JSON

来源：https://stackoverflow.com/questions/65536438/expand-json-with-unknown-keys-to-rows-with-mysql-json-table

3条答案

按热度按时间

kuhbmx9i1#

您可以通过使用ROW_NUMBER()窗口函数来使用enumarete，同时通过使用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

赞(0）回复(0）举报 2023-03-20

omqzjyyz2#

尝试在将JSON_KEYS作为行后直接执行JSON_EXTRACT：

WITH j AS (
      SELECT CAST('{"a": 1, "b": "-1", "c": null}' AS JSON) o UNION ALL
      SELECT CAST('{"x": 2, "y": "-2", "z": null}' AS JSON)
  )
SELECT k, JSON_EXTRACT(j.o, CONCAT('$."', jt.k, '"')) v
  FROM j
     , JSON_TABLE(JSON_KEYS(o), '$[*]' COLUMNS (k VARCHAR(200) PATH '$')) jt;

Barbaros的答案可以解决您提供的演示数据的问题，但是如果您的json对象在不同的键下具有相同的值，它可能不会得到您想要的。

赞(0）回复(0）举报 2023-03-20

ddhy6vgd3#

与EricW的答案相同，只是没有CTE，使用了显式交叉连接...

SELECT k, JSON_EXTRACT(json_data, concat('$.', k)) as v
FROM the_table t
CROSS JOIN JSON_TABLE(
    json_keys(json_data),
    '$[*]' COLUMNS(k VARCHAR(10) path '$')
) t2;

赞(0）回复(0）举报 2023-03-20

我来回答

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

3条答案

相关问题

热门标签

最新问答