set dolphin.lower_case_table_names=1;
create schema json_arrayagg_test;
set current_schema to 'json_arrayagg_test';
-- create table for test
create table City(District varchar(30), Name varchar(30), Population int);
insert into City values ('Capital Region','Canberra',322723);
insert into City values ('New South Wales','Sydney',3276207);
insert into City values ('New South Wales','Newcastle',270324);
insert into City values ('New South Wales','Central Coast',227657);
insert into City values ('New South Wales','Wollongong',219761);
insert into City values ('Queensland','Brisbane',1291117);
insert into City values ('Queensland','Gold Coast',311932);
insert into City values ('Queensland','Townsville',109914);
insert into City values ('Queensland','Cairns',92273);
insert into City values ('South Australia','Adelaide',978100);
insert into City values ('Tasmania','Hobart',126118);
insert into City values ('Victoria','Melbourne',2865329);
insert into City values ('Victoria','Geelong',125382);
insert into City values ('West Australia','Perth',1096829);
-- test function
SELECT 
  District AS State,
  JSON_ARRAYAGG(Name) AS "City/Name"
FROM City
GROUP BY State;
      State      |                       City/Name                        
-----------------+--------------------------------------------------------
 South Australia | ["Adelaide"]
 Capital Region  | ["Canberra"]
 West Australia  | ["Perth"]
 Victoria        | ["Melbourne", "Geelong"]
 Queensland      | ["Brisbane", "Gold Coast", "Townsville", "Cairns"]
 New South Wales | ["Sydney", "Newcastle", "Central Coast", "Wollongong"]
 Tasmania        | ["Hobart"]
(7 rows)

SELECT 
  District AS State,
  JSON_ARRAYAGG(Population) AS "City/Population"
FROM City
GROUP BY State;
      State      |          City/Population          
-----------------+-----------------------------------
 South Australia | [978100]
 Capital Region  | [322723]
 West Australia  | [1096829]
 Victoria        | [2865329, 125382]
 Queensland      | [1291117, 311932, 109914, 92273]
 New South Wales | [3276207, 270324, 227657, 219761]
 Tasmania        | [126118]
(7 rows)

CREATE TEMP TABLE foo (serial_num int, name text, type text);
INSERT INTO foo VALUES (847001,'t15','GE1043');
INSERT INTO foo VALUES (847002,'t16','GE1043');
INSERT INTO foo VALUES (847003,'sub-alpha','GESS90');
SELECT json_arrayagg(serial_num)
FROM foo;
      json_arrayagg       
--------------------------
 [847001, 847002, 847003]
(1 row)

SELECT json_arrayagg(type)
FROM foo;
         json_arrayagg          
--------------------------------
 ["GE1043", "GE1043", "GESS90"]
(1 row)

create temp table time_table(a date, b numeric);
insert into time_table values(20200822, 1);
insert into time_table values(20211001, 2);
insert into time_table values(20221204, 3);
select json_arrayagg(b) from time_table;
 json_arrayagg 
---------------
 [1, 2, 3]
(1 row)

select json_arrayagg(a) from time_table;
               json_arrayagg                
--------------------------------------------
 ["2020-08-22", "2021-10-01", "2022-12-04"]
(1 row)

select json_arrayagg(341522654875451.12345678901234567890123456789012345678901234567890);
                            json_arrayagg                             
----------------------------------------------------------------------
 [341522654875451.12345678901234567890123456789012345678901234567890]
(1 row)

drop schema json_arrayagg_test cascade;
NOTICE:  drop cascades to table city
reset json_arrayagg_test;
ERROR:  unrecognized configuration parameter "json_arrayagg_test"