Question 90321 in PostgreSQL Question posted in
The official documentation can be found here.

Select hierarchical JSON as table in PostgreSQL

I’m trying to select this hierarchical JSON as a table in PostgreSQL

The JSON script:

'{"NODES":[{"DESC_D":"fam","SEQ":"1","ID":"2304500","NODES":[{"DESC_D":"test 1","SEQ":"2.1","ID":"5214","NODES":[{"DESC_D":"test 1.1","SEQ":"3.1","ID":"999"}]},{"DESC_D":"test 2","SEQ":"2.2","ID":"74542"}]}]}'

The output I’m trying to get (click this link)

This is a solution a friend wrote.
It does the required but in a complex way, is there a simpler solution?


WITH RECURSIVE CTE(SEQ, DESC_D, ID, PARENT_ID, NODES) AS (
  SELECT json_extract_path_text(e.element, 'SEQ') SEQ,
         json_extract_path_text(e.element, 'DESC_D') DESC_D,
         json_extract_path_text(e.element, 'ID') ID,
         NULL PARENT_ID,
         json_extract_path(e.element, 'NODES') NODES
  FROM json_each('{"NODES":[{"DESC_D":"fam","SEQ":"1","ID":"2304500","NODES":[{"DESC_D":"test 1","SEQ":"2.1","ID":"5214","NODES":[{"DESC_D":"test 1.1","SEQ":"3.1","ID":"999"}]},{"DESC_D":"test 2","SEQ":"2.2","ID":"74542"}]}]}'::JSON) a(KEY, val)
  CROSS JOIN LATERAL json_array_elements(a.val) e(element)
  WHERE json_typeof(a.val) = 'array'
  UNION ALL
  SELECT json_extract_path_text(e.element, 'SEQ') SEQ,
         json_extract_path_text(e.element, 'DESC_D') DESC_D,
         json_extract_path_text(e.element, 'ID') ID,
         r.ID PARENT_ID,
         json_extract_path(e.element, 'NODES') NODES
  FROM CTE r
  CROSS JOIN LATERAL json_array_elements(r.NODES) e(element)
)
SELECT DISTINCT ON (ID) *
FROM CTE;
Tags:

2

Answers


  1. 0

    you can use the jsonpath type and functions and language :

    SELECT DISTINCT
       child->>'SEQ' AS seq
     , child->>'DESC_D' AS desc_d
     , child->>'ID' AS id
     , parent->>'ID' AS parent_id
     , child->>'NODES' AS nodes
  FROM jsonb_path_query('{"NODES":[{"DESC_D":"fam","SEQ":"1","ID":"2304500","NODES":[{"DESC_D":"test 1","SEQ":"2.1","ID":"5214","NODES":[{"DESC_D":"test 1.1","SEQ":"3.1","ID":"999"}]},{"DESC_D":"test 2","SEQ":"2.2","ID":"74542"}]}]}'
                       , '$.** ? (@.NODES.type() == "array")') AS parent
 CROSS JOIN LATERAL jsonb_array_elements(parent->'NODES') AS child
 ORDER BY seq

    Result :

    seq desc_d id parent_id nodes
    1 fam 2304500 null [{"ID": "5214", "SEQ": "2.1", "NODES": [{"ID": "999", "SEQ": "3.1", "DESC_D": "test 1.1"}], "DESC_D": "test 1"}, {"ID": "74542", "SEQ": "2.2", "DESC_D": "test 2"}]
    2.1 test 1 5214 2304500 [{"ID": "999", "SEQ": "3.1", "DESC_D": "test 1.1"}]
    2.2 test 2 74542 2304500 null
    3.1 test 1.1 999 5214 null

    see dbfiddle

    Login or Signup to reply.
  2. 0

    Your friend’s answer is correct (as is Edouard’s). A couple of things could perhaps make the recursive query easier to read. You can use the ->> operator, which basically executes the json_extract_path_text function. You can also omit the CROSS JOIN LATERAL because any set-returning function implies that join type when following the FROM with a comma. In your case, IDs are unique, so DISTINCT ON (ID) isn’t needed, but if your JSON could have duplicate IDs, you’ll need to put it back in.

    WITH RECURSIVE CTE(SEQ, DESC_D, ID, PARENT_ID, NODES) AS (
  SELECT e.element ->> 'SEQ' SEQ,
         e.element ->> 'DESC_D' DESC_D,
         e.element ->> 'ID' ID,
         NULL PARENT_ID,
         e.element -> 'NODES' NODES
  FROM json_each('{"NODES":[{"DESC_D":"fam","SEQ":"1","ID":"2304500","NODES":[{"DESC_D":"test 1","SEQ":"2.1","ID":"5214","NODES":[{"DESC_D":"test 1.1","SEQ":"3.1","ID":"999"}]},{"DESC_D":"test 2","SEQ":"2.2","ID":"74542"}]}]}'::JSON) a(KEY, val),
       json_array_elements(a.val) e(element)
  WHERE json_typeof(a.val) = 'array'
  UNION ALL
  SELECT e.element ->> 'SEQ' SEQ,
         e.element ->> 'DESC_D' DESC_D,
         e.element ->> 'ID' ID,
         r.ID PARENT_ID,
         e.element -> 'NODES' NODES
  FROM CTE r,
       json_array_elements(r.NODES) e(element)
)
SELECT *
FROM CTE;

    To make it easier to understand, just remember that the part about UNION ALL is always your starting point, and the part after is digging down. Just look at each part separately to help make sense of it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search