使用JSON_TABLE将ARRAY转换为Columns

Question

我有一个数据库列(名为"details"(，格式为JSON对象，包含以下数据：

'{"300-000012": {"is_complete": "False", "is_in_progress": "True"}, 
"300-000018": {"is_complete": "True", "is_in_progress": "False"}}'

我似乎无法将数组转换为列。我试过

SELECT mh.*, jt.*
FROM history AS mh,
JSON_TABLE (mh.details, '$[*]' 
COLUMNS (
NESTED PATH '$.*' COLUMNS (jt_complete VARCHAR(255) PATH '$.is_complete'),
NESTED PATH '$.*' COLUMNS (jt_progress VARCHAR(255) PATH '$.is_in_progress')
)
) AS jt)

但我收到一个错误代码

Error Code: 3143. Invalid JSON path expression

理想情况下，我会得到这样的东西：

details             jt_complete            jt_progress
300-000012             FALSE                  TRUE
300-000018              TRUE                  FALSE

如有任何帮助，我们将不胜感激。Thx

Answer 1

这是一个棘手的问题，因为对象的键是可变的。这意味着您需要分别提取每个对象的关键点和值。可以使用每个JSON_TABLE的序数列连接这些值，并将它们连接到：

SELECT mh.id, jk.details, jt.jt_complete, jt.jt_progress
FROM history mh
JOIN JSON_TABLE(
JSON_KEYS(mh.details),
'$[*]' COLUMNS (
rn FOR ORDINALITY,
details VARCHAR(10) PATH '$'
)
) jk
JOIN JSON_TABLE(
JSON_EXTRACT(mh.details, '$.*'),
'$[*]' COLUMNS (
rn FOR ORDINALITY,
jt_complete VARCHAR(10) PATH '$.is_complete',
jt_progress VARCHAR(10) PATH '$.is_in_progress'
)
) jt ON jt.rn = jk.rn

dbfiddle 演示