mysql - 列出 MySQL JSON 字段的所有数组元素

Question

我有一个 JSON 字段来保存帖子的标签。

id:1, content:'...', tags: ["tag_1", "tag_2"]

id:2, content:'...', tags: ["tag_3", "tag_2"]

id:3, content:'...', tags: ["tag_1", "tag_2"]

我只想列出所有标签及其受欢迎程度(甚至没有它们)，如下所示:

tag_2: 3,

tag_1: 2,

标签_3:1

Answer 1

这是设置:

create table t ( id serial primary key, content json);
insert into t set content = '{"tags": ["tag_1", "tag_2"]}';
insert into t set content = '{"tags": ["tag_3", "tag_2"]}';
insert into t set content = '{"tags": ["tag_1", "tag_2"]}';

如果你知道任何标签数组中标签的最大数量，你可以使用 UNION 提取所有标签:

select id, json_extract(content, '$.tags[0]') AS tag from t 
union
select id, json_extract(content, '$.tags[1]') from t;

+----+---------+
| id | tag     |
+----+---------+
|  1 | "tag_1" |
|  2 | "tag_3" |
|  3 | "tag_1" |
|  1 | "tag_2" |
|  2 | "tag_2" |
|  3 | "tag_2" |
+----+---------+

您需要与最长数组中的标签数量一样多的联合子查询。

然后您可以将其放入派生表中并对其执行聚合:

select tag, count(*) as count
from ( 
    select id, json_extract(content, '$.tags[0]') as tag from t 
    union 
    select id, json_extract(content, '$.tags[1]') from t
) as t2
group by tag
order by count desc;

+---------+-------+
| tag     | count |
+---------+-------+
| "tag_2" |     3 |
| "tag_1" |     2 |
| "tag_3" |     1 |
+---------+-------+

如果将标签存储在第二个表中而不是 JSON 数组中，这会更容易:

create table tags ( id bigint unsigned, tag varchar(20) not null, primary key (id, tag));
insert into tags set id = 1, tag = 'tag_1';
insert into tags set id = 1, tag = 'tag_2';
insert into tags set id = 2, tag = 'tag_3';
insert into tags set id = 2, tag = 'tag_2';
insert into tags set id = 3, tag = 'tag_1';
insert into tags set id = 3, tag = 'tag_2';

select tag, count(*) as count 
from tags
group by tag
order by count desc;

+-------+-------+
| tag   | count |
+-------+-------+
| tag_2 |     3 |
| tag_1 |     2 |
| tag_3 |     1 |
+-------+-------+

无论每个 ID 有多少标签，此解决方案都有效。您不需要知道每个 id 的标签列表的最大长度。

当您需要存储半结构化数据的“文档”时，JSON 非常有用，但前提是您将文档视为一个不可简化的数据值。一旦您需要访问文档的元素并对它们应用关系操作，面向文档的方法就会显示出它的弱点。