Re: WIP json generation enhancements

On 11/22/2012 05:54 AM, Dimitri Fontaine wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)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