WITH RECURSIVE supplytree AS
(
SELECT
id,
description,
parent_feature_id,
to_jsonb(feature) AS si_object,
CAST(description As varchar(1000)) AS si_fullname
FROM feature
WHERE parent_feature_id = 30
UNION ALL
SELECT
si.id,
si.description,
si.parent_feature_id,
to_jsonb(si) || jsonb_build_object('children', sp.si_object) AS si_object,
CAST(sp.si_fullname || '->' || si.description As varchar(1000)) As si_fullname
FROM feature As si
INNER JOIN supplytree AS sp ON (si.parent_feature_id = sp.id)
)
SELECT json_agg(row_to_json(ROW))
FROM (
SELECT
json_build_object(
'si_id', id,
'si_description', description,
'si_object', si_object,
'si_fullname', si_fullname
) AS children
FROM supplytree
ORDER BY id
) row
Last active
August 12, 2021 22:16
-
-
Save mknparreira/8121b42abe4318ca7aa216e6032d1f4a to your computer and use it in GitHub Desktop.
PostegreSQL | With Recursive
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
https://www.postgresonline.com/journal/archives/131-Using-Recursive-Common-table-expressions-to-represent-Tree-structures.html