From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Eli Murray <ejmurra2(at)illinimedia(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Building JSON objects |
Date: | 2015-03-27 18:36:43 |
Message-ID: | 5515A33B.9060002@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-03-27 18:38:42 | Re: Building JSON objects |
Previous Message | Arthur Silva | 2015-03-27 18:36:16 | Re: json-patch support? |