Andrew Dunstan <[email protected]> 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
