Re: WIP json generation enhancements - Mailing list pgsql-hackers

On Thu, Nov 22, 2012 at 4:54 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> 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.

I don't think there is any way a json_agg() function could reasonably
do this.  The only possible dataset it could work on would be for
homogeneously typed array-like data which is not very interesting for
the broader case of nested json productions. I think the right way to
do it is to work out the precise structure in sql and do the
transformation directly on the type using the already existing
transformation functions.

That said, recursive structures are a pain presently because postgres
composite types are not allowed to be recursive.  So ISTM getting that
restriction relaxed is the way to go; then you build it in sql and
just fire whatever xxx_to_json happens to be the most
appropriate...then your example would be a snap.

merlin