Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


CREATE FUNCTION nested_jsonb (a jsonb, b jsonb) RETURNS jsonb LANGUAGE sql IMMUTABLE AS 
$$
SELECT CASE
         WHEN a IS null THEN b
         ELSE b || jsonb_build_object('child', a)
       END ;
$$ ;

CREATE AGGREGATE nested_jsonb(c jsonb)
( sfunc = nested_jsonb 
, stype = jsonb 
) ;

CREATE FUNCTION



CREATE AGGREGATE


SELECT nested_jsonb (a.content ORDER BY a.id DESC)
  FROM jsonb_array_elements
            ('[ { "id": 1, "name": "level 1"}
              , { "id": 3, "name": "level 2"}
              , { "id": 8, "name": "level 3"}
              ]' :: jsonb
            ) WITH ORDINALITY AS a(content,id)


nested_jsonb


{"id": 1, "name": "level 1", "child": {"id": 3, "name": "level 2", "child": {"id": 8, "name": "level 3"}}}



SELECT 1


CREATE FUNCTION nested_object_jsonb (input jsonb) RETURNS jsonb LANGUAGE sql IMMUTABLE AS 
$$
WITH RECURSIVE t (content, id) AS
( SELECT input[-1] AS content
       , jsonb_array_length(input) - 2 AS id
  UNION ALL
  SELECT input[t.id] || jsonb_build_object('child', t.content)
       , id - 1
    FROM t
   WHERE id >= 0
)
SELECT t.content
  FROM t
 WHERE t.id < 0 ;
$$ ;

CREATE FUNCTION


SELECT nested_object_jsonb
            ('[ { "id": 1, "name": "level 1"}
              , { "id": 3, "name": "level 2"}
              , { "id": 8, "name": "level 3"}
              ]' :: jsonb
            )


nested_object_jsonb


{"id": 1, "name": "level 1", "child": {"id": 3, "name": "level 2", "child": {"id": 8, "name": "level 3"}}}



SELECT 1