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.