Re: Building JSON objects

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.

On Fri, Mar 27, 2015 at 12:47 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 03/27/2015 10:40 AM, Eli Murray wrote:
>
>> Hi All,
>>
>> I'm trying to create an array of JSON objects from an existing table.
>> I've created a new table:
>>
>> "CREATE TABLE json(data json[]);"
>>
>> Now i need to populate it with key value pairs from another table. I'd
>> like to create the objects to look like:
>>
>> { "code": rawdata.deptcode, "name": rawdata.deptname }
>>
>> But I'm having difficulty understanding the syntax to do this because I
>> find the docs to be rather difficult to parse.
>>
>> I've tried running the query:
>>
>> "INSERT INTO json(data) json_build_object(SELECT DISTINCT deptcode,
>> deptname AS code, name FROM rawdata);"
>>
>> but it returns a syntax error.
>>
>
> That would be?
>
> What version of Postgres?
>
>
>> I've also tried running:
>>
>> "INSERT INTO json(data) row_to_json(SELECT DISTINCT deptcode, deptname
>> FROM rawdata);"
>>
>> but no dice.
>>
>> Does anyone know the proper syntax to create an array of JSON objects
>> from an existing table?
>>
>
> json_build_array?
> http://www.postgresql.org/docs/9.4/interactive/functions-json.html
>
>
>> --
>> Senior Web Developer at The Daily Illini
>> ejmurra2(at)illinimedia(dot)com <mailto:ejmurra2(at)illinimedia(dot)com>
>> (815) 985-8760
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Senior Web Developer at The Daily Illini
ejmurra2(at)illinimedia(dot)com
(815) 985-8760