表结构

article,"CREATE TABLE `article` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_at` bigint(20) DEFAULT NULL,
  `update_at` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `category_id` bigint(20) DEFAULT NULL,
  `tags_string` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

table data

[
  {
    "id": 1,
    "title": "标题1",
    "content": "content111",
    "create_at": 1640966400,
    "update_at": 123123123,
    "user_id": 12123132,
    "category_id": 2,
    "tags_string": "[\"linux\", \"dev\"]"
  },
  {
    "id": 2,
    "title": "标题2",
    "content": "111",
    "create_at": 111,
    "update_at": 11,
    "user_id": 1,
    "category_id": 1,
    "tags_string": "[\"linux\"]"
  }
]

SQL

select a.tag,count(a.tag) as count from article, json_table(tags_string,'$[*]' columns (tag varchar(40) PATH '$')) a group by a.tag;

Result

linux,2
dev,1