列出MySQL JSON字段的所有数组元素 [英] List all array elements of a MySQL JSON field

问题描述

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

I have a JSON field to save post's tags.

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

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

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

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

I just want to list all tags with their popularities (or even without them) something like this:

标签_2:3，

标签_1:2，

tag_3: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提取所有标签:

If you know the maximum number of tags in any tag array, you can extract all the tags using 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" |
+----+---------+

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

You need as many unioned subqueries as the number of tags in the longest array.

然后，您可以将其放在派生表中并对其进行汇总:

Then you can put this in a derived table and perform an aggregation on it:

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数组中，这会更容易:

This would be easier if you stored tags in a second table instead of in a JSON array:

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的标签列表的最大长度.

This solutions works no matter how many tags per id you have. You don't need to know the max length of the list of tags per id.

当您需要存储半结构化数据的文档"时，但仅当您将文档视为一个不可约的数据值时，JSON才是不错的选择.一旦您需要访问文档的元素并对其应用关系操作，面向文档的方法就会显示出它的弱点.

JSON is nice when you need to store a 'document' of semi-structured data, but only when you treat the document as one irreducible data value. As soon as you need to access elements of the document and apply relational operations to them, the document-oriented approach shows its weakness.

这篇关于列出MySQL JSON字段的所有数组元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！