Thread: JSON row_to_json_array
Hello,
Why isn't there a row_to_json_array(record) funtion?
I dont need the column names. That are only unused bytes
greetings
Tjibbe
On Sat, Sep 21, 2013 at 10:26 PM, Tjibbe R <tjibbe@rijpma.org> wrote: > Hello, > > Why isn't there a row_to_json_array(record) funtion? > I'm not a json expert, but it could be the output would not be a json structure (after all, it would be a simple array)? Luca
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);
On 23 September 2013 08:42, Luca Ferrari <fluca1978@infinito.it> wrote:
On Sat, Sep 21, 2013 at 10:26 PM, Tjibbe R <tjibbe@rijpma.org> wrote:
> Hello,
>
> Why isn't there a row_to_json_array(record) funtion?
>
I'm not a json expert, but it could be the output would not be a json
structure (after all, it would be a simple array)?
Luca
On Mon, Sep 23, 2013 at 10: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(). > Maybe looping with json_each could simplify the solution? Luca
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