专栏首页性能与架构体验 MySQL 8.0 JSON聚合函数

体验 MySQL 8.0 JSON聚合函数

MySQL 最近的动作很快,已经计划推出 8.0 版本,会新增很多新特性

在 5.7 中,JSON 已经被正式支持,但在 SQL 中对 JSON 的处理能力较弱,8.0 中这部分能力会加强,例如新增了这两个JSON聚合函数

JSON_ARRAYAGG()
JSON_OBJECTAGG()

通过JSON聚合函数,可以在 SQL 中直接把数据整合为JSON结构,非常简单

基础用法

创建测试表

CREATE TABLE `t1` (
   `key`  varchar(8) DEFAULT NULL,
   `grp` varchar(8) DEFAULT NULL,
   `val` varchar(8)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

添加测试数据

INSERT INTO t1(`key`, `grp`, `val`) VALUES
("key1", "g1", "v1"),
("key2", "g1", "v2"),
("key3", "g2", "v3");

在查询中使用聚合函数

把字段 ‘key’ 的所有值整合为一个JSON数组

mysql> SELECT JSON_ARRAYAGG(`key`) AS `keys` FROM t1;
+--------------------------+
| keys                     |
+--------------------------+
| ["key1", "key2", "key3"] |
+--------------------------+
1 row in set (0.00 sec)

分组聚合

mysql> SELECT grp, JSON_ARRAYAGG(`key`) AS `keys_grouped` FROM t1  GROUP BY grp;
+------+------------------+
| grp  | keys_grouped     |
+------+------------------+
| g1   | ["key1", "key2"] |
| g2   | ["key3"]         |
+------+------------------+
2 rows in set (0.00 sec)

mysql> SELECT grp, JSON_OBJECTAGG(`key`, val) AS `key_val_grouped` FROM t1 GROUP BY grp;
+------+------------------------------+
| grp  | key_val_grouped              |
+------+------------------------------+
| g1   | {"key1": "v1", "key2": "v2"} |
| g2   | {"key3": "v3"}               |
+------+------------------------------+
2 rows in set (0.00 sec)

把某两列的值整合为JSON对象

mysql> SELECT JSON_OBJECTAGG(`key`, val) AS `key_val` FROM t1;
+------------------------+
| key_val                |
+------------------------+
| {
"key1": "v1", 
"key2": "v2", 
"key3": "v3"
} |
+------------------------+
1 row in set (0.00 sec)

场景示例

描述

例如一个产品表,其中包含产品的通用属性(名称、价格...)

产品还有自己的单独属性,例如 电脑会包含 cpu/内存 等型号、衣服会包含 颜色、材质 等

引申出另外两张表:扩展属性表、属性值表

需求

查询出产品的所有信息,包括所有属性及其值,并整合为JSON结构

实现

建表

// 产品表
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) DEFAULT NULL,
  `manufacturer` varchar(120) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

// 扩展属性表
CREATE TABLE `attribute` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

// 属性值表
CREATE TABLE `value` (
  `prod_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `value` text,
  PRIMARY KEY (`prod_id`,`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

添加测试数据

// 插入一些属性
INSERT INTO attribute(id, name) VALUES
(1, "color"),
(2, "material"),
(3, "style"),
(4, "bulb_type"),
(5, "usage"),
(6, "cpu_type"),
(7, "cpu_speed"),
(8, "weight"),
(9, "battery_life"),
(10, "fuel_type");
// 插入一个产品:台灯
INSERT INTO product(id, name, manufacturer, price) VALUES
(1, "LED Desk Lamp", "X", 26);
// 插入台灯对应的属性值
INSERT INTO value VALUES
(1, 1, "black"),
(1, 2, "plastic"),
(1, 3, "classic"),
(1, 4, "LED"),
(1, 5, "Indoor use only");
// 插入一个产品:电脑
INSERT INTO product(id, name, manufacturer, price) VALUES
(2, "Laptop", "Y", 800);
// 插入电脑对应的属性值
INSERT INTO value VALUES
(2, 1, "blue"),
(2, 6, "quad core"),
(2, 7, "3400 mhz"),
(2, 8, "2,1 kg"),
(2, 9, "9h");
// 插入一个产品:烧烤架
INSERT INTO product(id, name, manufacturer, price) VALUES
(3, "Grill", "Z", 300);
// 插入对应属性值
INSERT INTO value VALUES
(3, 1, "black"),
(3, 8, "5 kg"),
(3, 10, "gas");

查询

// 关联3张表,按产品ID分组
// 把查询结果聚合为JSON对象
SELECT
JSON_OBJECT("key", p.id,
            "title", p.name,
            "manufacturer", p.manufacturer,
            "price", p.price,
            "specifications", JSON_OBJECTAGG(a.name, v.value)) as product
FROM 
product as p JOIN value as v 
    ON p.id=v.prod_id 
JOIN attribute as a 
    ON a.id=v.attribute_id
GROUP BY v.prod_id;

结果示例

{
  "key": 1,
  "price": 26,
  "title": "LED Desk Lamp",
  "manufacturer": "X",
  "specifications": {
    "color": "black",
    "style": "classic",
    "usage": "Indoor use only",
    "material": "plastic",
    "bulb_type": "LED"
  }
} 
......

小结

本文整理自:

http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions

MySQL 8 实验版本下载地址:

https://labs.mysql.com/

(选择 MySQL Server 8.0.0 Optimizer)

本文分享自微信公众号 - 性能与架构(yogoup),作者:杜亦舒

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-10-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql 5.7 主从复制的多线程复制配置方式

    数据库复制的主要性能问题就是数据延时 为了优化复制性能,Mysql 5.6 引入了 “多线程复制” 这个新功能 但 5.6 中的每个线程只能处理一个数据库,所以...

    dys
  • 图解 Java 线程生命周期

    创建线程(NEW),然后线程做自己的工作(RUNNABLE),做完之后就终止了(TERMINATED)。

    dys
  • 一个小巧实用的User-Agent解析库

    UAParser.js 有时我们需要判断客户端的一些信息,如浏览器的类型、型号等,然后进行相应处理,例如,如果是使用微信访问的,就怎么怎么处理。 这就需要从 U...

    dys
  • 前端技术前沿9

    install-node-msi-version-on-windows-step1

    达达前端
  • Apache按天分隔日志

    windows下修改你的虚拟主机配置中增加如下内容: ErrorLog “|bin/rotatelogs.exe logs/5201314-%Y_%m_%d.e...

    苦咖啡
  • JSON.stringify驯服循环引用的对象

    前端黑板报
  • Golang语言 监控文件变化小程序.

    package main import ( "log" "github.com/go-fsnotify/fsnotify" ) func main()...

    李海彬
  • Bio、Nio、Aio的用法系列之NIO客户端(三)

    上一篇文章我们提到了NIO,大家应该对NIO有了一定的了解,接下来我们继续学习NIO的客户端实现

    用户1257393
  • 第一章 正则表达式字符匹配攻略

    第一章 正则表达式字符匹配攻略 正则表达式是匹配模式,要么匹配字符,要么匹配位置。请记住这句话。 然而关于正则如何匹配字符的学习,大部分人都觉得这块比较杂乱。...

    程序猿DD
  • Golang语言 监控文件变化小程序.

    package main import ( "log" "github.com/go-fsnotify/fsnotify" ) func main()...

    李海彬

扫码关注云+社区

领取腾讯云代金券