mysql - 从 MySQL 中的 JSON 对象中提取 key 对值

Question

从 MySQL JSON 数据字段中，我从数组中提取数据，如下所示:

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

返回:

[{
"id": 111056, 
"hint": null, 
"slug": "email", 
"label": "E-mail", 
"value": null, 
"field_value": "<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="8df9e8fef9cde8f5ece0fde1e8a3eee2e0" rel="noreferrer noopener nofollow">[email protected]</a>", 
"placeholder": null
}, {
"id": 111057, 
"hint": null, 
"slug": "name", 
"label": "Imię", 
"value": null, 
"field_value": "Aneta", 
"placeholder": null
}]

我还可以提取单列:

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

并返回以下结果:

[<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="225647515662475a434f524e470c414d4f" rel="noreferrer noopener nofollow">[email protected]</a>, Aneta]

但是如何提取 field_value 和 label 作为 key 对？

首选输出是包含对的单个多行字符串:

label: field_value
label: field_value
...

使用上面显示的示例，它将得到以下输出:

E-mail: <a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ccb8a9bfb88ca9b4ada1bca0a9e2afa3a1" rel="noreferrer noopener nofollow">[email protected]</a>
Imię: Aneta

首选单行，因为我有多个这样的数组可以从各个字段中提取。

Answer 1

以下是将键名称提取为行的示例:

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

输出:

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

现在您可以将其加入到值中:

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);

输出:

+---+-------------+------------------+
| n | keyname     | value            |
+---+-------------+------------------+
| 0 | id          | 111056           |
| 0 | hint        | null             |
| 0 | slug        | email            |
| 0 | label       | E-mail           |
| 0 | value       | null             |
| 0 | field_value | <a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="98ecfdebecd8fde0f9f5e8f4fdb6fbf7f5" rel="noreferrer noopener nofollow">[email protected]</a> |
| 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             |
+---+-------------+------------------+

我正在使用一个实用程序表n，它只填充了整数。

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

如果这看起来像是很多复杂的工作，那么也许教训是，当您希望 SQL 表达式作用于 JSON 文档中的离散字段时，在 JSON 中存储数据并不容易。