使用MYSQL提取嵌套JSON对象中的数组

Question

我正试图用MYSQL从JSON对象中提取一个数组

SELECT json_extract(jsonObjectValue,'$[*].name') as array FROM `TEST` WHERE name='jsonObject'

上面的查询结果是

...
["elem1", "elem1", "elem2"]
["elem5", "elem1", "elem2", "elem4"]
...

我试着这样做来提取数组：

SELECT json_extract(json_extract(jsonObjectValue,'$[*].name'),'$[*]') as array FROM `TEST` WHERE name='jsonObject'

所需的结果如下：

...
"elem1"
"elem1"
"elem2"
"elem5"
"elem1"
"elem2"
"elem4"
...

但实际结果是：

...
["elem1", "elem1", "elem2"]
["elem5", "elem1", "elem2", "elem4"]
...

我还尝试将JSON提取中的'$[*]'更改为'$[0]'，它只显示数组的第一个元素。

更新

要重现问题，请运行以下查询：

CREATE TABLE `TEST` (
`jsonObjectValue` varchar(1000) NOT NULL,
`name` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

对于数据运行：

INSERT INTO `TEST` (`jsonObjectValue`, `name`) VALUES
('[{"name":"elem1"},{"name":"elem1"},{"name":"elem2"}]', 
'JsonObject'),
('test', 'name'),
('test2', 'test2'),
('[{"name":"elem5"},{"name":"elem1"},{"name":"elem2"}, 
{"name":"elem4"}]', 'jsonObject');

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

Answer 1

SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(arrays.array, ',', numbers.num), ',', -1)) element
FROM ( SELECT TRIM('[' FROM TRIM(']' FROM json_extract(jsonObjectValue,'$[*].name'))) as array 
FROM `TEST` 
WHERE name='jsonObject') arrays,
( SELECT 1 num UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) numbers
WHERE numbers.num <= LENGTH(arrays.array) - LENGTH(REPLACE(arrays.array, ',', '')) + 1;

小提琴

PS。生成的数字数量必须不小于单独数组中元素的最大数量，否则将丢失一些值。