使用json_table实现json转关系表,进行聚合统计

Jimmy Lee

学习思考|Jul 15, 2022|Last edited: 2022-7-26|
type
Post
status
Published
date
Jul 15, 2022
slug
mysql-json
summary
tags
MYSQL
JSON
category
学习思考
icon
Update time
Jul 26, 2022 03:40 AM
Internal status
READY TO PUBLISH
password

表结构

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
 

开始订阅我的关于终生学习, 生产力以及知识管理的文章. 订阅后, 您将收到我的精选文章.

©2014-2024 Jimmy Lee. All rights reserved. 公众号: 技术管理方法论
Powered By My Lovely Children