MySQL5.7高级函数：JSON_ARRAYAGG和JSON_OBJECT的使用

前置准备

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`
(
    `id`                     bigint(20)                                                              NOT NULL,
    `name`                   varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci            NOT NULL                 DEFAULT ''          COMMENT '姓名',
	`relative_ids`	         json															         NOT NULL		                              COMMENT '亲人',
	`friend_ids`	         varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci			 NOT NULL		          DEFAULT ''          COMMENT '朋友',
    `sex`                    int                                                                     NOT NULL                 DEFAULT 0           COMMENT '性别 1:男,2:女',
    `object_id`              bigint(20)                                                              NOT NULL                 DEFAULT 0           COMMENT '对象id',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = '用户表';

INSERT INTO `t_user` VALUES (1, '张伟', '[]', '3,4', 1, 5);
INSERT INTO `t_user` VALUES (2, '李明', '[4, 5]', '', 1, 0);
INSERT INTO `t_user` VALUES (3, '王强', '[]', '', 1, 4);
INSERT INTO `t_user` VALUES (4, '雨婷', '[]', '', 2, 3);
INSERT INTO `t_user` VALUES (5, '蕾蕾', '[]', '', 2, 1);

DROP TABLE IF EXISTS `t_hobby`;
CREATE TABLE `t_hobby`
(
    `id`                     bigint(20)                                                              NOT NULL,
    `name`                   varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci            NOT NULL                 DEFAULT ''          COMMENT '爱好',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = '爱好表';

INSERT INTO `t_hobby` VALUES (1, '篮球');
INSERT INTO `t_hobby` VALUES (2, '足球');
INSERT INTO `t_hobby` VALUES (3, '游泳');
INSERT INTO `t_hobby` VALUES (4, '跑步');
INSERT INTO `t_hobby` VALUES (5, '健身');
INSERT INTO `t_hobby` VALUES (6, '音乐');
INSERT INTO `t_hobby` VALUES (7, '旅行');
INSERT INTO `t_hobby` VALUES (8, '读书');
INSERT INTO `t_hobby` VALUES (9, '电影');
INSERT INTO `t_hobby` VALUES (10, '游戏');
INSERT INTO `t_hobby` VALUES (11, '绘画');
INSERT INTO `t_hobby` VALUES (12, '摄影');
INSERT INTO `t_hobby` VALUES (13, '写作');
INSERT INTO `t_hobby` VALUES (14, '编程');
INSERT INTO `t_hobby` VALUES (15, '烹饪');
INSERT INTO `t_hobby` VALUES (16, '园艺');
INSERT INTO `t_hobby` VALUES (17, '钓鱼');
INSERT INTO `t_hobby` VALUES (18, '滑雪');
INSERT INTO `t_hobby` VALUES (19, '滑冰');
INSERT INTO `t_hobby` VALUES (20, '登山');

DROP TABLE IF EXISTS `t_user_hobby`;
CREATE TABLE `t_user_hobby`
(
    `id`                     bigint(20)              NOT NULL,
    `user_id`                bigint(20)              NOT NULL                 DEFAULT 0          COMMENT '用户id',
	`hobby_id`               bigint(20)              NOT NULL                 DEFAULT 0          COMMENT '爱好id',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = '用户爱好关联表';

INSERT INTO `t_user_hobby` VALUES (1, 1, 1);
INSERT INTO `t_user_hobby` VALUES (2, 1, 2);
INSERT INTO `t_user_hobby` VALUES (3, 2, 3);
INSERT INTO `t_user_hobby` VALUES (4, 2, 12);
INSERT INTO `t_user_hobby` VALUES (5, 3, 10);
INSERT INTO `t_user_hobby` VALUES (6, 3, 4);
INSERT INTO `t_user_hobby` VALUES (7, 3, 5);
INSERT INTO `t_user_hobby` VALUES (8, 4, 7);
INSERT INTO `t_user_hobby` VALUES (9, 4, 1);
INSERT INTO `t_user_hobby` VALUES (10, 5, 10);

数据分析

1、王强和雨婷、张伟和蕾蕾互为情侣

2、王强和雨婷都是张伟的朋友

3、雨婷和蕾蕾都是李明的亲人  

4、张伟的爱好分别是：1:篮球、2:足球

李明的爱好分别是：3:游泳、12:摄影

王强的爱好分别是：10:游戏、4:跑步、5:健身

雨婷的爱好分别是：7:旅行、1:篮球

蕾蕾的爱好分别是：10:游戏

需求实现

1、查询出“李明”的个人信息以及他的亲人信息

SELECT
	usr.id,
	usr.NAME,
	usr.relative_ids,
	(
	SELECT
		JSON_ARRAYAGG( JSON_OBJECT( 'id', relative.id, 'name', relative.NAME ) ) 
	FROM
		t_user relative 
	WHERE
		relative.id IN ( SELECT relative_ids.id FROM JSON_TABLE ( usr.relative_ids, '$[*]' COLUMNS ( id BIGINT PATH '$' ) ) AS relative_ids ) 
	) AS relative_infos,
	usr.friend_ids,
	usr.sex,
	usr.object_id 
FROM
	t_user usr 
WHERE
	id = 2

2、查询出“张伟”的个人信息以及他的朋友信息

SELECT
	usr.id,
	usr.NAME,
	usr.relative_ids,
	usr.friend_ids,
	(
	SELECT
		JSON_ARRAYAGG( JSON_OBJECT( 'id', friend.id, 'name', friend.NAME ) ) 
	FROM
		t_user friend 
	WHERE
		FIND_IN_SET( friend.id, usr.friend_ids ) > 0 
	) AS friend_infos,
	usr.sex,
	usr.object_id 
FROM
	t_user usr 
WHERE
	id = 1 ​

3、查询出“有对象”的个人信息以及他(她)的对象信息

SELECT
	usr.id,
	usr.NAME,
	usr.relative_ids,
	( SELECT JSON_OBJECT( 'id', object.id, 'name', object.NAME ) FROM t_user object WHERE object.id = usr.object_id ) AS object_info,
	usr.friend_ids,
	usr.sex,
	usr.object_id 
FROM
	t_user usr 
WHERE
	usr.object_id != 0

4、查询出所有用户的爱好信息

SELECT
	usr.id,
	usr.NAME,
	usr.relative_ids,
	usr.friend_ids,
	usr.sex,
	usr.object_id,
	(
	SELECT
		JSON_ARRAYAGG(
		JSON_OBJECT( 'id', hobby.id, 'name', hobby.NAME )) 
	FROM
		t_hobby hobby
		LEFT JOIN t_user_hobby user_hobby ON user_hobby.hobby_id = hobby.id 
	WHERE
		user_hobby.user_id = usr.id 
	) AS hobby_infos 
FROM
	t_user usr

备注

mysql的版本必须要>=5.7