PostegreSQL | With Recursive

postegre-with-recursive.md

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

https://www.postgresonline.com/journal/archives/131-Using-Recursive-Common-table-expressions-to-represent-Tree-structures.html