On 11/22/2012 05:54 AM, Dimitri Fontaine wrote:
Andrew Dunstan <and...@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.
the function only takes a single argument and aggregates all the values
into a json array. If the arguments are composites they will produce
json objects.
People complained that to get a resultset as json you have to do in 9.2
select array_to_json(array_agg(q)) ...
which is both a bit cumbersome and fairly inefficient. json_agg(q) is
equivalent to the above expression but is both simpler and much more
efficient.
If you want a tree structured object you'll need to construct it
yourself - this function won't do the nesting for you. That's beyond its
remit.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
