Select hierarchical JSON as table in PostgreSQL-CodePudding

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;

CodePudding user response：

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

CodePudding user response：

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.