I have a set of data, lets say an array of json objects. like so:
[
{ "id": 1, "name": "level 1"},
{ "id": 3, "name": "level 2"},
{ "id": 8, "name": "level 3"}
]
and i wanne nest those items based on their order in the array
{
"id": 1,
"name": "level 1",
"child": {
"id": 3,
"name": "level 2",
"child": {
"id": 8,
"name": "level 3"
}
}
}
So far the only way i can think of is building a custom aggregate, but that's not only a lot of work, but also hard in terms of deployment (it's not my database)
Are there ways of doing this in a query?
So far I'm thinking in the line of using unnest to create different rows for each item in the array. Then adding a rownumber to the rows to maintain order and then somehow add the item from row 2 into the item of row 1.. but so far i've not been able to do that.. Also it has to be recursive which i don't know if it's gonne work
In other words.. I need help..
Solution using a custom aggregate
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
) ;
Then run the following query with your array of json objects :
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)
and you get the expected result
{"id": 1, "name": "level 1", "child": {"id": 3, "name": "level 2", "child": {"id": 8, "name": "level 3"}}}
Solution using a recursive query
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 ;
$$ ;
Then run the following query :
SELECT nested_object_jsonb
('[ { "id": 1, "name": "level 1"}
, { "id": 3, "name": "level 2"}
, { "id": 8, "name": "level 3"}
]' :: jsonb
)
and you get the expected result
{"id": 1, "name": "level 1", "child": {"id": 3, "name": "level 2", "child": {"id": 8, "name": "level 3"}}}
see dbfiddle