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.
Second cut:
postgres@test=# select array_to_json(array_agg(row(fld_1, fld_2))) from
build_object_test;
array_to_json
-------------------------------------------------------------------------------
[{"f1":"fld1_test1","f2":"fld1_test1"},{"f1":"fld1_test2","f2":"fld1_test2"}]
(1 row)
--
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
