Andrew Dunstan <andrew@dunslane.net> writes:
> Here is a WIP patch for enhancements to json generation.
>
> First, there is the much_requested json_agg, which will aggregate rows
> directly to json. So the following will now work:
>
> select json_agg(my_table) from mytable;
> select json_agg(q) from (<myquery here>) q;
Awesome, thanks!
How do you handle the nesting of the source elements? I would expect a
variant of the aggregate that takes two input parameters, the datum and
the current nesting level.
Consider a tree table using parent_id and a recursive query to display
the tree. You typically handle the nesting with an accumulator and a
call to repeat() to prepend some spaces before the value columns. What
about passing that nesting level (integer) to the json_agg()?
Here's a worked out example:
CREATE TABLE parent_child ( parent_id integer NOT NULL, this_node_id integer NULL ); INSERT INTO
parent_child(parent_id, this_node_id) VALUES (0, 1); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1,
2); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1, 3); INSERT INTO parent_child (parent_id,
this_node_id)VALUES (1, 4); INSERT INTO parent_child (parent_id, this_node_id) VALUES (2, 5); INSERT INTO
parent_child(parent_id, this_node_id) VALUES (2, 6); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4,
7); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4, 8); INSERT INTO parent_child (parent_id,
this_node_id)VALUES (4, 9); INSERT INTO parent_child (parent_id, this_node_id) VALUES (9, 10); WITH RECURSIVE
tree(id,level, parents) AS ( SELECT this_node_id as id, 0 as level, '{}'::int[] as parents FROM
parent_child WHERE parent_id = 0 UNION ALL SELECT this_node_id as id, t.level + 1,
t.parents|| c.parent_id FROM parent_child c JOIN tree t ON t.id = c.parent_id ) SELECT json_agg(id,
level) FROM tree;
I've left the parents column in the query above as a debug facility, but
it's not needed in that case.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
