mysql 8 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 productFROM

product as p JOIN value as v

ON p.id=v.prod_id

JOIN attribute as a

ON a.id=v.attribute_idGROUP 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)

点击 “阅读原文” 查看 文章列表