Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE TABLE head(i INTEGER);

INSERT INTO head VALUES
   (1),
   (2),
   (3);

CREATE TABLE

INSERT 0 3

CREATE TABLE details(i INTEGER, c CHAR(1));

INSERT INTO details VALUES
   (1,'a'),
   (1,'b'),
   (1,'c'),
   (2,'d'),
   (2,'e'),
   (2,'f'),
   (3,'g'),
   (3,'h'),
   (3,'i');

CREATE TABLE

INSERT 0 9

SELECT 
   JSON_BUILD_OBJECT('head', i, 
                     'details',(SELECT JSON_AGG(c) 
                                FROM details 
                                WHERE details.i = head.i)
                    )
FROM head
WHERE i=2;

json_build_object

{"head" : 2, "details" : ["d", "e", "f"]}

SELECT 1