JSON JSON_EXTRACT with MySQL

Question

I'm using the JSON_EXTRACT with MYSQL and using this command:

SET @j = '{"id" : "1"}';
SELECT JSON_EXTRACT(@j, '$.id')

the result is

"1"

but, when I use

SET @j = '[{"id" : "1"}, {"id" : "2"}]';
SELECT JSON_EXTRACT(@j, '$.id')

the result is

NULL

I expected this result

"1"
"2"

Any sugestion? I want the list of ID'S from JSON.

you're using two different types of objects. the first is an object that would have an id property. the second is an array. you need to loop through the array first to access each id — TheRealMrCrowley
– TheRealMrCrowley, Commented Aug 29, 2017 at 19:17
If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them., see JSON_EXTRACT. See db-fiddle. — wchiquito
– wchiquito, Commented Aug 31, 2017 at 8:56

Alejandro Landini · Accepted Answer · 2018-05-01 23:54:43Z

2

try this:

SET @j = '[{"id" : "1"}, {"id" : "2"}]';
SELECT JSON_EXTRACT(@j,'$[*].id')

the result is:

["1", "2"]

answered May 1, 2018 at 23:54

Alejandro Landini

1192 silver badges6 bronze badges

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

1 Answer 1