前提：mysql版本 8.0.25

1、表如下

CREATE TABLE `app_tag` (
  `pkg` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'package',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '应用名称',
  `tags` json DEFAULT NULL COMMENT 'tag',
  PRIMARY KEY (`pkg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='app包tag';

数据如下

INSERT INTO `app_tag` VALUES ('com.netease.cloudmusic', '\r\n\r\n网易云音乐', '[\"MV\", \"电台\", \"听歌\"]');
INSERT INTO `app_tag` VALUES ('com.p1.mobile.putong', '探探', '[\"交友\"]');
INSERT INTO `app_tag` VALUES ('com.smile.gifmaker', '\r\n\r\n快手', '[\"短视频\"]');
INSERT INTO `app_tag` VALUES ('com.xiaomi.youpin', '小米有品', '[\"电商\", \"小米\", \"智能家居\"]');

pkg                   |name   |tags                |
----------------------+-------+--------------------+
com.netease.cloudmusic|¶¶网易云音乐|["MV", "电台", "听歌"]  |
com.p1.mobile.putong  |探探     |["交友"]              |
com.smile.gifmaker    |¶¶快手   |["短视频"]             |
com.xiaomi.youpin     |小米有品   |["电商", "小米", "智能家居"]|

1、查询 tags 包含 交友 tag的数据

select *
from app_tag
where JSON_CONTAINS(tags->'$[*]','"交友"','$')

-- 模糊查询
select *
from app_tag
where tags -> '$' like "%游%"

结果如下：

pkg                 |name|tags  |
--------------------+----+------+
com.p1.mobile.putong|探探  |["交友"]|

2、将tags每个标签一行进行查询

SELECT name,pkg,
  JSON_EXTRACT(tags, CONCAT('$[', idx, ']')) AS fishes
FROM app_tag
JOIN (
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(tags, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY pkg, idx;

查询结果如下

name   |pkg                   |fishes|
-------+----------------------+------+
¶¶网易云音乐|com.netease.cloudmusic|"MV"  |
¶¶网易云音乐|com.netease.cloudmusic|"电台"  |
¶¶网易云音乐|com.netease.cloudmusic|"听歌"  |
探探     |com.p1.mobile.putong  |"交友"  |
¶¶快手   |com.smile.gifmaker    |"短视频" |
小米有品   |com.xiaomi.youpin     |"电商"  |
小米有品   |com.xiaomi.youpin     |"小米"  |
小米有品   |com.xiaomi.youpin     |"智能家居"|