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.
>

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@aklaver.com