JSON_OBJECTAGG

功能描述

JSON_OBJECTAGG函数将第一个参数作为键和第二个参数作为值的键值对聚合为一个JSON对象。

注意事项

• 该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。
• 如果结果集没有任何行，此函数将返回 NULL。

语法格式

JSON_OBJECTAGG(key, value)

参数说明

key, value

键值对。

示例

1、创建测试表并插入数据。

create table table_1160251(District varchar(30), Name varchar(30), Population int);
insert into table_1160251 values ('Capital Region','Canberra',322723);
insert into table_1160251 values ('New South Wales','Sydney',3276207);
insert into table_1160251 values ('New South Wales','Newcastle',270324);
insert into table_1160251 values ('New South Wales','Central Coast',227657);
insert into table_1160251 values ('New South Wales','Wollongong',219761);
insert into table_1160251 values ('Queensland','Brisbane',1291117);
insert into table_1160251 values ('Queensland','Gold Coast',311932);
insert into table_1160251 values ('Queensland','Townsville',109914);
insert into table_1160251 values ('Queensland','Cairns',92273);
insert into table_1160251 values ('South Australia','Adelaide',978100);
insert into table_1160251 values ('Tasmania','Hobart',126118);
insert into table_1160251 values ('Victoria','Melbourne',2865329);
insert into table_1160251 values ('Victoria','Geelong',125382);
insert into table_1160251 values ('West Australia','Perth',1096829);

2、调用JSON_OBJECTAGG函数。

SELECT JSON_OBJECTAGG(Name, Population) AS "City/Population" FROM table_1160251;
SELECT District AS State,JSON_OBJECTAGG(Name, Population) AS "City/Population" FROM table_1160251 GROUP BY State;

返回结果分别为：

                          City/Population

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
 {"Perth": 1096829, "Cairns": 92273, "Hobart": 126118, "Sydney": 3276207, "Geelong": 125382, "Adelaide": 978100, "Brisbane": 1291117, "Canberra": 322723, "Melbourne": 2865329, "Newcastle": 270324, "Go
ld Coast": 311932, "Townsville": 109914, "Wollongong": 219761, "Central Coast": 227657}
(1 row)


      State      |                                     City/Population
-----------------+-----------------------------------------------------------------------------------------
 West Australia  | {"Perth": 1096829}
 Queensland      | {"Cairns": 92273, "Brisbane": 1291117, "Gold Coast": 311932, "Townsville": 109914}
 New South Wales | {"Sydney": 3276207, "Newcastle": 270324, "Wollongong": 219761, "Central Coast": 227657}
 Tasmania        | {"Hobart": 126118}
 Victoria        | {"Geelong": 125382, "Melbourne": 2865329}
 South Australia | {"Adelaide": 978100}
 Capital Region  | {"Canberra": 322723}
(7 rows)