Extract array in nested JSON object with MYSQL

Question

I'm trying to extract an array from a JSON object with MYSQL

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

The query above has this as a result

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

I tried doing to extract the array by doing this:

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

The desired result would look like this:

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

but the actual result is:

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

I also tried to change '$[*]' inside the JSON extract to '$[0]' it only shows the first element of the array.

Update

to reproduce the issue run these queries:

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

and for the data run:

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');

Any help would be appreciated.

You're out of options I am afraid. A brute force approach that uses $[x].name as path where x comes from a table of numbers 0...999 might work. — Salman Arshad
– Salman Arshad, Commented Mar 4, 2020 at 12:42
Priovide a fiddle with some sample data (or CREATE TABLE + INSERT INTO scripts). — Akina
– Akina, Commented Mar 4, 2020 at 13:19
the version is MySQL 5 Treate intermediate array as a string, and parse it using common string functions and generated numbers table. — Akina
– Akina, Commented Mar 4, 2020 at 13:21

Akina · Accepted Answer · 2020-03-04 13:50:43Z

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;

fiddle

PS. The amount of numbers generated must be not less than the max amount of elements in separate array - if not some values will be lost.

answered Mar 4, 2020 at 13:50

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamel Mili Over a year ago

Thanks for the answer @Akina this is far too complicated, and I'm only solving part of the problem, The final result would way too large to implement...

Akina Over a year ago

@KamelMili Update your MySQL to last version, and use JSON_TABLE() function.

Collectives™ on Stack Overflow

Extract array in nested JSON object with MYSQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related