1

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": "test@example.com", 
"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:

[test@example.com, 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: test@example.com
Imię: Aneta

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gacek
  • 10,184
  • 9
  • 54
  • 87
  • 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 Nov 18 '21 at 18:22

2 Answers2

4

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 | test@example.com |
| 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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    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.) – MatBailie Nov 18 '21 at 18:22
  • 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 – Gacek Nov 21 '21 at 17:29
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

CodingBee
  • 1,011
  • 11
  • 8