使用JSON_TABLE获取一个嵌套的JSON数组

Question

我有一个Json数组，而看起来如下

[{"keyId": "aded5b0107bb5a936604bcb6f", "keyNames": ["abc1, abc2"], "keyDesc": "test"}]

我使用JSON_TABLE来获取所有的值，我已经写了以下查询:

SELECT j.keyId, j.keyNames, j.keyDesc
FROM table t, JSON_TABLE(value, '$[*]' 
COLUMNS(
keyId TEXT PATH '$.keyId' 
keyNames TEXT PATH '$.keyNames',
keyDesc TEXT PATH '$.keyDesc')
) AS j;

我得到以下输出:

keyId                        keyNames   keyDesc
aded5b0107bb5a936604bcb6f    NULL       test

如何修改查询，使keyNames的值为"abc1, abc2"，而不是NULL?

Answer 1

因为它是一个新数组，所以需要嵌套PATH

CREATE TABLE t
( `value` TEXT)
;

INSERT INTO t
( `value`)
VALUES
('[{"keyId": "aded5b0107bb5a936604bcb6f", "keyNames": ["abc1, abc2"], "keyDesc": "test"}]')

SELECT  
j.keyId, j.keyNames,  j.keyDesc
FROM t, JSON_TABLE(value, '$[*]' 
COLUMNS(
keyId TEXT PATH '$.keyId' 
,NESTED PATH '$.keyNames[*]' COLUMNS (keyNames varchar(10) PATH '$')
, keyDesc TEXT PATH '$.keyDesc'
)
) AS j;

keyId | keyNames | keyDesc:------------------------ |:--------- |:------aded5b0107bb5a936604bcb6f | abc1, abc2 | test

db<此处小提琴>