How can i convert an array (or jsonb_array) to a nested json object?

34 Views Asked by At

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

0

There are 0 best solutions below