数据API 案例 开发者 关于
掌握聚合最新动态了解行业最新趋势
API接口,开发服务,免费咨询服务
新闻动态 > 媒体报道

体验 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"
  }
} 
......

小结


本文整理自:

MySQL 8.0 Labs: JSON aggregation functions

MySQL 8 实验版本下载地址: MySQL Labs

(选择 MySQL Server 8.0.0 Optimizer)

性能与架构.jpg

原文来自:性能与架构

掌握聚合最新动态了解行业最新趋势
API接口,开发服务,免费咨询服务
新闻动态 > 媒体报道
体验 MySQL 8.0 JSON聚合函数
发布:2016-10-11

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"
  }
} 
......

小结


本文整理自:

MySQL 8.0 Labs: JSON aggregation functions

MySQL 8 实验版本下载地址: MySQL Labs

(选择 MySQL Server 8.0.0 Optimizer)

性能与架构.jpg

原文来自:性能与架构

电话 0512-88869195
数 据 驱 动 未 来
Data Drives The Future