jsonpostgresqlaggregate

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


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

  • 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