CODEWITHSUNDEEP
jsonpostgresqlrecursion
Miguel Flores
Miguel Flores

Reputation: 193

Recursive parent child JSON Postgresql

I have the following recursive PostgreSQL table.

CREATE TABLE public."data" (
    id int4 NULL,
    parentid int4 NULL,
    menu varchar NULL
);

I am trying to create a JSON object from the table.

[
  {
    "menu": "home",
    "children": [
      {
        "menu": "home 1",
        "children": []
      },
      {
        "menu": "home 2",
        "children": [
          {
            "menu": "home 2.1",
            "children": []
          }
        ]
      }
    ]
  },
  {
    "menu": "config",
    "children": []
  }
]

How could I create a hierarchal object like this?

Upvotes: 0

Views: 1113

Answers (1)

Edouard
Edouard

Reputation: 7065

First you should use the jsonb format instead of the json format in postgres, see the documentation here :

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys..

Then, assuming your table is a set of (id, menu) tuples and parentid is the id of the parent of this tuple, you can try :

CREATE VIEW parent_children (parent, children, root, cond) AS
(   SELECT jsonb_build_object('menu', p.menu, 'children', '[]' :: jsonb) :: text AS parent
         , jsonb_agg(jsonb_build_object('menu', c.menu, 'children', '[]' :: jsonb)) :: text AS children
         , array[c.parentid] AS root
         , array[c.parentid] AS cond
      FROM public.data AS c
      LEFT JOIN public.data AS p
        ON p.id = c.parentid
     GROUP BY c.parentid
) ;

WITH RECURSIVE list(parent, children, root, cond) AS
(   SELECT parent, children, root, cond
      FROM parent_children
     WHERE root = array[NULL]   -- start with the root parents
    UNION
    SELECT p.parent
         , replace(p.children, c.parent, replace(c.parent, '[]', c.children))
         , p.root
         , p.cond || c.cond
      FROM list AS p
     INNER JOIN parent_children AS c
        ON position(c.parent IN p.children) > 0
       AND NOT p.cond @> c.root -- condition to avoid circular path
)
SELECT children :: jsonb
  FROM list AS l
  ORDER BY array_length(cond, 1) DESC
  LIMIT 1 ;

Upvotes: 2

Related Questions