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..