Extract key-pair values from JSON objects in MySQL

Question

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.

If you want concise code, don't store data as json in a relational database, use the native data structures that sql is Designed to work with. Such that each field in each row holds just one data value, not a document of data. — MatBailie
– MatBailie, Commented Nov 18, 2021 at 18:22

Bill Karwin · Accepted Answer · 2021-11-18 18:07:47Z

7

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.

answered Nov 18, 2021 at 18:07

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

MatBailie Over a year ago

I wish I could vote twice, once for the answer and once for the last paragraph. (The ease of storage as JSON is often associated with deferring complexity to data retrieval.)

Gacek Over a year ago

This doesn't answer my question, but I think it would be good starting point. Yeah, unfortunately I do not have anything to say how this data is being stored. I need to work with what I have. But you are absolutely right

CodingBee · Accepted Answer · 2022-09-05 09:28:22Z

0

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

answered Sep 5, 2022 at 9:28

CodingBee

1,22914 silver badges8 bronze badges

Collectives™ on Stack Overflow

Extract key-pair values from JSON objects in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related