CODEWITHSUNDEEP
mysqljson
Mordechai
Mordechai

Reputation: 745

in MYSQL how do I get key-value pairs from JSON without having to specify keys?

in most languages it's a simple task, for each key-value pair print keyname, value. I don't want to specify a key name for each pair, just get all of them. But I can't seem to find a way to do it in MySQL. For example:

create table myjson (icol JSON);  
INSERT INTO myjson VALUES('{"tmpdir":"/tmp"}');  
select JSON_VALUE(icol,'$.tmpdir') `with_key`  
, JSON_VALUE(icol,'$') `without key`  
,JSON_KEYS(icol,'$') `just key`  
from myjson;

I'd want to return a columnar data set of
key_name value
"tmpdir" "/tmp"

thank you.

Upvotes: 1

Views: 781

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562250

select j.key_name, 
  json_unquote(json_extract(icol, concat('$.', j.key_name))) as value
from myjson cross join json_table(json_keys(icol,'$'), '$[*]' 
  columns (key_name text path '$')) j;
+----------+-------+
| key_name | value |
+----------+-------+
| tmpdir   | /tmp  |
+----------+-------+

Upvotes: 2

Related Questions