Re: Building JSON objects

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(at)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(dot)klaver(at)aklaver(dot)com