Search Postgresql Archives

Re: Building JSON objects

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 03/27/2015 11:12 AM, Eli Murray wrote:
I'm running psql --version 9.4.1

Also, it may be worth noting that rawdata.deptname and rawdata.deptcode
are both text data types.

The errors I'm getting are:

ERROR:  syntax error at or near "json_build_object"
LINE 1: insert into json(data) json_build_object(SELECT DISTINCT dep...

and

ERROR:  syntax error at or near "row_to_json"
LINE 1: insert into json(data) row_to_json(SELECT DISTINCT deptname,...

As per this advice
<http://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array>
from stack overflow, I also tried running:

INSERT INTO json(data) array_to_json(array_agg(SELECT DISTINCT deptcode,
deptname FROM rawdata));

Which returned:

ERROR:  syntax error at or near "array_to_json"
LINE 1: INSERT INTO json(data) array_to_json(array_agg(SELECT DISTIN...

Also tried json_build_array with the same result.

I did try to use commands from the documentation page you linked but I
just don't understand how I should be building the query. In my head,
the query should basically say, "Build objects from distinct rows in
rawdata, push each object into the array, and then insert the array into
the json table." I could do it in javascript or python but I'm pretty
green when it comes to SQL. I know it's probably simple, but I'm having
a hell of a time trying to figure it out.

Anyway, thanks for the suggestion and letting me rubber duck debug off
of you.


Yeah, I am still wrapping my head around using the JSON features.

A first cut:

create table build_object_test(fld_1 varchar, fld_2 varchar);

insert into  build_object_test values ('fld1_test1', 'fld1_test1');
insert into  build_object_test values ('fld1_test2', 'fld1_test2');

postgres@test=# select row_to_json(row(fld_1, fld_2)) from build_object_test;
              row_to_json
---------------------------------------
 {"f1":"fld1_test1","f2":"fld1_test1"}
 {"f1":"fld1_test2","f2":"fld1_test2"}
(2 rows)



--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux