体验 MySQL 8.0 JSON聚合函数

原创

mob604756f2af3b 2021-04-22 11:02:10 ©著作权

文章标签 java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者mob604756f2af3b的原创作品，请联系作者获取转载授权，否则将追究法律责任

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/内存等型号、衣服会包含颜色、材质等

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

体验 MySQL 8.0 JSON聚合函数_java

需求

查询出产品的所有信息，包括所有属性及其值，并整合为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"
  }
} 
......