CODEWITHSUNDEEP
mysqldatabasemysql-json
Gacek
Gacek

Reputation: 10312

Extract key-pair values from JSON objects in MySQL

From MySQL JSON data field, I'm extracting data from array like so:

SELECT 
data ->> '$.fields[*]' as fields 
FROM some_database...

which returns:

[{
"id": 111056, 
"hint": null, 
"slug": "email", 
"label": "E-mail", 
"value": null, 
"field_value": "[email protected]", 
"placeholder": null
}, {
"id": 111057, 
"hint": null, 
"slug": "name", 
"label": "Imię", 
"value": null, 
"field_value": "Aneta", 
"placeholder": null
}]

I can also extract single column:

SELECT 
data ->> '$.fields[*].field_value' as fields 
FROM some_database...

and that returns the following result:

[[email protected], Aneta]

But how can I extract field_value alongside with label as key-pairs?

Preferred output would be a single multi-row string containing pairs:

label: field_value
label: field_value
...

Using example shown above it would get me following output:

E-mail: [email protected]
Imię: Aneta

One-liner preferred as I have multiple of such arrays to extract from various fields.

Upvotes: 2

Views: 4819

Answers (2)

CodingBee
CodingBee

Reputation: 1199

You can use JSON_VALUE:

select JSON_VALUE (json_value_col, '$.selected_key') as selected_value from user_details ;

You can also use JSON_EXTRACT:

select JSON_EXTRACT (json_value_col, '$.selected_key') as selected_value from user_details ;

For more details refer: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562250

Here's an example of extracting the key names as rows:

select j.keyname from some_database 
cross join json_table(
  json_keys(data->'$[0]'), 
  '$[*]' columns (
    keyname varchar(20) path '$'
  )
) as j;

Output:

+-------------+
| keyname     |
+-------------+
| id          |
| hint        |
| slug        |
| label       |
| value       |
| field_value |
| placeholder |
+-------------+

Now you can join that to the values:

select n.n, j.keyname,
  json_unquote(json_extract(f.data, concat('$[', n.n, ']."', j.keyname, '"'))) as value
from some_database as d
cross join json_table(
  json_keys(d.data->'$[0]'),
  '$[*]' columns (
    keyname varchar(20) path '$'
  )
) as j
cross join n
join some_database as f on n.n < json_length(f.data);

Output:

+---+-------------+------------------+
| n | keyname     | value            |
+---+-------------+------------------+
| 0 | id          | 111056           |
| 0 | hint        | null             |
| 0 | slug        | email            |
| 0 | label       | E-mail           |
| 0 | value       | null             |
| 0 | field_value | [email protected] |
| 0 | placeholder | null             |
| 1 | id          | 111057           |
| 1 | hint        | null             |
| 1 | slug        | name             |
| 1 | label       | Imię             |
| 1 | value       | null             |
| 1 | field_value | Aneta            |
| 1 | placeholder | null             |
+---+-------------+------------------+

I'm using a utility table n which is just filled with integers.

create table n (n int primary key);
insert into n values (0),(1),(2),(3)...;

If this seems like a lot of complex work, then maybe the lesson is that storing data in JSON is not easy, when you want SQL expressions to work on the discrete fields within JSON documents.

Upvotes: 6

Related Questions