Re: JSON row_to_json_array

On Mon, Sep 23, 2013 at 3:26 AM, Tjibbe R <tjibbe(at)rijpma(dot)org> wrote:
> Yes it would be a simple array, but it costs a lot of coding. Because every
> item in the array need to be inserted in the function to_json().
>
> Example:
>
> DECLARE
> _arr json[];
> FOR row in SELECT * FROM persons LOOP
> _arr := array_append (_arr, ARRAY(to_json(row.id),
> to_json(row.birthdate), to_json (row.active_bool),to_json(row.level));
> END LOOP;
> RETURN to_json(_arr);

as coded that will be very slow for large arrays due to increasing
concatenation times. better to do it in one swoop. also let's
generalize it so that the function will work for any table and be a
pure sql expression (no function):

test data:
postgres=# create table foo(a text, b int, c date);
CREATE TABLE
postgres=# insert into foo values ('a', 6, clock_timestamp()), ('b',
7, clock_timestamp());
INSERT 0 2

convert record to array:
postgres=# select array_to_json(array(select
(json_each(row_to_json(foo))).value)) from foo;
array_to_json
----------------------
["a",6,"2013-09-23"]
["b",7,"2013-09-23"]

do the entire table. the extra to_json calls do add some overhead but
it should still beat the loop.

postgres=# select array_to_json(array(select
array_to_json(array(select (json_each(row_to_json(foo))).value)) from
foo));
array_to_json
---------------------------------------------
[["a",6,"2013-09-23"],["b",7,"2013-09-23"]]

merlin