MariaDB/MySQL -使用JSON_TABLE将键和值从json对象转换为行

waxmsbnn  于 2023-01-30  发布在  Mysql
关注(0)|答案(3)|浏览(312)

使用Mariadb 10.6-在下面的示例中,我尝试将json对象的条目转换为表行:

SELECT *
FROM JSON_TABLE('{ 
   "1": [1, 123.25], 
   "10": [2, 110.5], 
   "100": [3, 105.75] 
}', '$.*' COLUMNS (
    col1 decimal(13,2) PATH '$',
    col2 int PATH '$[0]',
    col3 decimal(17,2) PATH '$[1]'
)) table1

结果是:
| 列1|列2|第3栏|
| - ------|- ------|- ------|
| 零|1个|一百二十三点二五|
| 零|第二章|一百一十块五毛|
| 零|三个|一百零五点七五分|
有什么方法可以用属性键("1"、"10"、"100")填充"col1"吗?
我猜有一些"关键字"来引用这个键,但是我在MariaDB或MySQL的文档中找不到任何相关信息。
我已经编写了一个例程,通过循环JSON_KEYS的输出来创建一个临时表,但是如果我可以使用JSON_TABLE来完成这项工作,那将更加优雅。

bgibtngc

bgibtngc1#

这是使用CROSS JOINJSON_TABLEJSON_KEYS的另一种方法:
JSON_KEYS(json)会给予["1", "10", "100"]
CROSS JOIN用于从["1", "10", "100"]生成多行

WITH data AS
( 
 SELECT '{
           "1": [1, 123.25], 
           "10": [2, 110.5], 
           "100": [3, 105.75] 
         }' AS json
)
SELECT k.key, c.col2, c.col3
  FROM data
 CROSS JOIN JSON_TABLE(
                       JSON_KEYS(json), 
                       '$[*]' COLUMNS(
                                       rowid FOR ORDINALITY, 
                                       key TEXT PATH '$'
                                      )
                      ) k
 INNER JOIN 
   (SELECT cols.*
      FROM data,
           JSON_TABLE(
                      json,
                      '$.*' COLUMNS(
                                     rowid FOR ORDINALITY,
                                     col2 int PATH '$[0]',
                                     col3 decimal(17, 2) PATH '$[1]'
                                    )
                      ) AS cols) AS c
    ON c.rowid = k.rowid;

demo here

jgwigjjp

jgwigjjp2#

这里有一种不用常规的方法:

  • 使用JSON_TABLE提取json值,并使用FOR ORDINALITY提取行号
  • 使用JSON_KEYS解压缩你的密钥
  • 对于每条记录,使用JSON_EXTRACT提取对应于第i个排名值的第i个关键字,排名值由行号给出
SELECT JSON_EXTRACT(JSON_KEYS(@json), 
                    CONCAT('$[', table1.rowid-1, ']')) AS col1, 
       table1.col2,
       table1.col3
FROM JSON_TABLE(@json, '$.*' COLUMNS (
    rowid FOR ORDINALITY,
    col2 int PATH '$[0]',
    col3 decimal(17,2) PATH '$[1]'
)) table1

输出:
| 列1|列2|第3栏|
| - ------|- ------|- ------|
| "1"|1个|一百二十三点二五|
| "十"|第二章|一百一十块五毛|
| "一百"|三个|一百零五点七五分|
检查here演示。

6jjcrrmo

6jjcrrmo3#

我回答我自己的问题:很抱歉,显然没有任何本地选项可以用JSON_TABLE引用json对象中的键名,目前发布的两个变通方法都很好,我最终使用了这两种方法的混合:

SET @json = '{ "1": [1, 123.25], "10": [2, 110.5], "100": [3, 105.75] }';

SELECT
    col1,
    JSON_EXTRACT(@json, CONCAT('$."', col1, '"[0]')) col2,
    JSON_EXTRACT(@json, CONCAT('$."', col1, '"[1]')) col3
FROM JSON_TABLE(JSON_KEYS(@json), '$[*]' COLUMNS (col1 varchar(20) PATH '$')) t1;

相关问题