Re: JSON row_to_json_array - Mailing list pgsql-novice

From Merlin Moncure
Subject Re: JSON row_to_json_array
Date
Msg-id CAHyXU0xtjEUKtUwqNuq80=nCKUjdJyVB0ADVO-J9Jr=-Bo+qhQ@mail.gmail.com
Whole thread Raw
In response to Re: JSON row_to_json_array  (Tjibbe R <tjibbe@rijpma.org>)
List pgsql-novice
On Mon, Sep 23, 2013 at 3:26 AM, Tjibbe R <tjibbe@rijpma.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


pgsql-novice by date:

Previous
From: Luca Ferrari
Date:
Subject: Re: JSON row_to_json_array
Next
From: James David Smith
Date:
Subject: Tablespaces