Postgres recursive query with row_to_json

I have a table in postgres 9.3.5 that looks like this:

CREATE TABLE customer_area_node ( id bigserial NOT NULL, customer_id integer NOT NULL, parent_id bigint, name text, description text, CONSTRAINT customer_area_node_pkey PRIMARY KEY (id) ) 

I am requesting with:

 WITH RECURSIVE c AS ( SELECT *, 0 as level, name as path FROM customer_area_node WHERE customer_id = 2 and parent_id is null UNION ALL SELECT customer_area_node.*, c.level + 1 as level, c.path || '/' || customer_area_node.name as path FROM customer_area_node join c ON customer_area_node.parent_id = c.id ) SELECT * FROM c ORDER BY path; 

this seems to work for creating paths like building1 / floor1 / room1, building1 / floor1 / room2, etc.

What I would like to do is easily turn this into json, which represents a tree structure that I was told what I can do with row_to_json.

As a reasonable alternative, in any other way I can format the data in a more efficient mechanism so that I can actually turn it into an actual tree structure without a ton of string.splits on /.

Is there an easy way to do this with row_to_json?

+5
source share
4 answers

You cannot do this with a regular recursive CTE, because it is almost impossible to set the json value in your hierarchy. But you can reverse this: create a tree, starting from its leaves, to its root:

 -- calculate node levels WITH RECURSIVE c AS ( SELECT *, 0 as lvl FROM customer_area_node -- use parameters here, to select the root first WHERE customer_id = 2 AND parent_id IS NULL UNION ALL SELECT customer_area_node.*, c.lvl + 1 as lvl FROM customer_area_node JOIN c ON customer_area_node.parent_id = c.id ), -- select max level maxlvl AS ( SELECT max(lvl) maxlvl FROM c ), -- accumulate children j AS ( SELECT c.*, json '[]' children -- at max level, there are only leaves FROM c, maxlvl WHERE lvl = maxlvl UNION ALL -- a little hack, because PostgreSQL doesn't like aggregated recursive terms SELECT (c).*, array_to_json(array_agg(j)) children FROM ( SELECT c, j FROM j JOIN c ON j.parent_id = c.id ) v GROUP BY vc ) -- select only root SELECT row_to_json(j) json_tree FROM j WHERE lvl = 0; 

And this will work even with PostgreSQL 9.2 +

SQLFiddle

Update : an option that should also handle rogue flyer nodes (which are located at a level between 1 and the maximum level):

 WITH RECURSIVE c AS ( SELECT *, 0 as lvl FROM customer_area_node WHERE customer_id = 1 AND parent_id IS NULL UNION ALL SELECT customer_area_node.*, c.lvl + 1 FROM customer_area_node JOIN c ON customer_area_node.parent_id = c.id ), maxlvl AS ( SELECT max(lvl) maxlvl FROM c ), j AS ( SELECT c.*, json '[]' children FROM c, maxlvl WHERE lvl = maxlvl UNION ALL SELECT (c).*, array_to_json(array_agg(j) || array(SELECT r FROM (SELECT l.*, json '[]' children FROM cl, maxlvl WHERE l.parent_id = (c).id AND l.lvl < maxlvl AND NOT EXISTS (SELECT 1 FROM c lp WHERE lp.parent_id = l.id)) r)) children FROM (SELECT c, j FROM c JOIN j ON j.parent_id = c.id) v GROUP BY vc ) SELECT row_to_json(j) json_tree FROM j WHERE lvl = 0; 

This should work on PostgreSQL 9.2+ too, but I can't verify it. (I can only test for 9.5+ right now).

These solutions can process any column in any hierarchical table, but will always add the typed lvl JSON property to their output int .

http://rextester.com/YNU7932

+7
source

Sorry for the very late answer, but I think I found an elegant solution that could be the accepted answer to this question.

Based on the amazing β€œlittle hack” found by @pozs, I came up with a solution that:

  • solves the problem of "rogue" with a very small code (using the predicate NOT EXISTS )
  • avoids the entire calculation process / state of the entire level
 WITH RECURSIVE customer_area_tree("id", "customer_id", "parent_id", "name", "description", "children") AS ( -- tree leaves (no matching children) SELECT c.*, json '[]' FROM customer_area_node c WHERE NOT EXISTS(SELECT * FROM customer_area_node AS hypothetic_child WHERE hypothetic_child.parent_id = c.id) UNION ALL -- pozs awesome "little hack" SELECT (parent).*, json_agg(child) AS "children" FROM ( SELECT parent, child FROM customer_area_tree AS child JOIN customer_area_node parent ON parent.id = child.parent_id ) branch GROUP BY branch.parent ) SELECT json_agg(t) FROM customer_area_tree t LEFT JOIN customer_area_node AS hypothetic_parent ON(hypothetic_parent.id = t.parent_id) WHERE hypothetic_parent.id IS NULL 

Update

Tested with very simple data , it really works, but, as posz noted in the comment, with data samples , some nodes of the rogue leaves are forgotten. But I found out that with even more complex data , the previous answer also does not work, because only nodes of rogue leaflets having a common ancestor with "nodes" of the maximum level "are caught (when" 1.2.5.8 "does not exist," 1.2.4 " and "1.2.5" are absent because they do not have a common ancestor with any leaf of the "maximum level" node).

So here is a new sentence mixing posz to work with mine, extracting the NOT EXISTS subquery and making it an internal UNION , using the duplicate UNION removal options (using the jsonb comparison capabilities):

 <!-- language: sql --> WITH RECURSIVE c_with_level AS ( SELECT *, 0 as lvl FROM customer_area_node WHERE parent_id IS NULL UNION ALL SELECT child.*, parent.lvl + 1 FROM customer_area_node child JOIN c_with_level parent ON parent.id = child.parent_id ), maxlvl AS ( SELECT max(lvl) maxlvl FROM c_with_level ), c_tree AS ( SELECT c_with_level.*, jsonb '[]' children FROM c_with_level, maxlvl WHERE lvl = maxlvl UNION ( SELECT (branch_parent).*, jsonb_agg(branch_child) FROM ( SELECT branch_parent, branch_child FROM c_with_level branch_parent JOIN c_tree branch_child ON branch_child.parent_id = branch_parent.id ) branch GROUP BY branch.branch_parent UNION SELECT c.*, jsonb '[]' children FROM c_with_level c WHERE NOT EXISTS (SELECT 1 FROM c_with_level hypothetical_child WHERE hypothetical_child.parent_id = c.id) ) ) SELECT jsonb_pretty(row_to_json(c_tree)::jsonb) FROM c_tree WHERE lvl = 0; 

Tested at http://rextester.com/SMM38494 ;)

+4
source

Developed the answer pozs a little further to get recursive leaves with their subtrees. So this answer really returns a complete tree.

 CREATE OR REPLACE FUNCTION pg_temp.getTree(bigint) RETURNS TABLE( id bigint, customer_id integer, parent_id bigint, name text, description text, children json ) AS $$ WITH RECURSIVE relations AS ( SELECT can.id, can.customer_id, can.parent_id, can.name, can.description, 0 AS depth FROM customer_area_node can WHERE can.id = $1 UNION ALL SELECT can.id, can.customer_id, can.parent_id, can.name, can.description, relations.depth + 1 FROM customer_area_node can JOIN relations ON can.parent_id = relations.id AND can.id != can.parent_id ), maxdepth AS ( SELECT max(depth) maxdepth FROM relations ), rootTree as ( SELECT r.* FROM relations r, maxdepth WHERE depth = maxdepth UNION ALL SELECT r.* FROM relations r, rootTree WHERE r.id = rootTree.parent_id AND rootTree.id != rootTree.parent_id ), mainTree AS ( SELECT c.id, c.customer_id, c.parent_id, c.name, c.description, c.depth, json_build_array() children FROM relations c, maxdepth WHERE c.depth = maxdepth UNION ALL SELECT (relations).*, array_to_json( array_agg(mainTree) || array( SELECT t FROM ( SELECT l.*, json_build_array() children FROM relations l, maxdepth WHERE l.parent_id = (relations).id AND l.depth < maxdepth AND l.id NOT IN ( SELECT id FROM rootTree ) ) r JOIN pg_temp.getTree(r.id) t ON r.id = t.id )) children FROM ( SELECT relations, mainTree FROM relations JOIN mainTree ON ( mainTree.parent_id = relations.id AND mainTree.parent_id != mainTree.id ) ) v GROUP BY v.relations ) SELECT id, customer_id, parent_id, name, description, children FROM mainTree WHERE id = $1 $$ LANGUAGE SQL; SELECT * FROM customer_area_node can JOIN pg_temp.getTree(can.id) t ON t.id = can.id WHERE can.parent_id IS NULL; 
0
source

@llmar Turk, I get this error on execution: there is no FROM clause record for table "can

0
source

Source: https://habr.com/ru/post/1201864/


All Articles