Building JSON objects

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@illinimedia.com <mailto:ejmurra2@illinimedia.com>
(815) 985-8760

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt;
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.klaver@aklaver.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@illinimedia.com <mailto:ejmurra2@illinimedia.com>
(815) 985-8760

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On March 27, 2015 01:12:52 PM Eli Murray wrote:

ERROR: syntax error at or near "json_build_object"
LINE 1: insert into json(data) json_build_object(SELECT DISTINCT dep...

You may want to review the syntax of the INSERT command, i.e. this doesn't
have anything to do with JSON:

INSERT INTO foo ( bar, baz, quux, froz ) VALUES (NULL, 12, 'box', now())

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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&gt;
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Mar 27, 2015 at 11:31 AM, Jan de Visser <jan@de-visser.net> wrote:

On March 27, 2015 01:12:52 PM Eli Murray wrote:

ERROR: syntax error at or near "json_build_object"
LINE 1: insert into json(data) json_build_object(SELECT DISTINCT dep...

You may want to review the syntax of the INSERT command, i.e. this doesn't
have anything to do with JSON:

INSERT INTO foo ( bar, baz, quux, froz ) VALUES (NULL, 12, 'box', now())

​or..

INSERT INTO json(data) SELECT json_build_object(SELECT DISTINCT dep...)

​David J.

On 27 March 2015 at 19:12, Eli Murray <ejmurra2@illinimedia.com> 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,...

Yea, because the insert syntax is a little bit different, try this:

insert into json(data) SELECT json_build...
or
insert into json(data) SELECT row_to_json...

regards,
Szymon

On March 27, 2015 11:38:42 AM David G. Johnston wrote:

On Fri, Mar 27, 2015 at 11:31 AM, Jan de Visser <jan@de-visser.net> wrote:

On March 27, 2015 01:12:52 PM Eli Murray wrote:

ERROR: syntax error at or near "json_build_object"
LINE 1: insert into json(data) json_build_object(SELECT DISTINCT dep...

You may want to review the syntax of the INSERT command, i.e. this doesn't
have anything to do with JSON:

INSERT INTO foo ( bar, baz, quux, froz ) VALUES (NULL, 12, 'box', now())

​or..

INSERT INTO json(data) SELECT json_build_object(SELECT DISTINCT dep...)

​David J.

Serves me right for bringing the snark without properly reading the OP :-)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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&gt;
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@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks to you all for the replies. Adrian, your solution is working for me
without errors but it's not actually inserting anything. I'll keep fiddling
with it and see if I can get what I want but I'm confident now that I'm on
the right path. As a backup, I did what I wanted to in Javascript and wrote
it to a file that I serve from my server when users request it. Thanks
again!

On Fri, Mar 27, 2015 at 1:49 PM, Jan de Visser <jan@de-visser.net> wrote:

On March 27, 2015 11:38:42 AM David G. Johnston wrote:

On Fri, Mar 27, 2015 at 11:31 AM, Jan de Visser <jan@de-visser.net>

wrote:

On March 27, 2015 01:12:52 PM Eli Murray wrote:

ERROR: syntax error at or near "json_build_object"
LINE 1: insert into json(data) json_build_object(SELECT DISTINCT

dep...

You may want to review the syntax of the INSERT command, i.e. this

doesn't

have anything to do with JSON:

INSERT INTO foo ( bar, baz, quux, froz ) VALUES (NULL, 12, 'box',

now())

​or..

INSERT INTO json(data) SELECT json_build_object(SELECT DISTINCT dep...)

​David J.

Serves me right for bringing the snark without properly reading the OP :-)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On Fri, Mar 27, 2015 at 12:30 PM, Eli Murray <ejmurra2@illinimedia.com>
wrote:

Thanks to you all for the replies. Adrian, your solution is working for me
without errors but it's not actually inserting anything. I'll keep fiddling
with it and see if I can get what I want but I'm confident now that I'm on
the right path. As a backup, I did what I wanted to in Javascript and wrote
it to a file that I serve from my server when users request it. Thanks
again!

​Adrian didn't write it as an insert statement (hence the lack of the word
INSERT)...though you've been pointed the correct direction on that topic in
other responses.

David J.

On 03/27/2015 12:30 PM, Eli Murray wrote:

Thanks to you all for the replies. Adrian, your solution is working for
me without errors but it's not actually inserting anything. I'll keep
fiddling with it and see if I can get what I want but I'm confident now
that I'm on the right path. As a backup, I did what I wanted to in
Javascript and wrote it to a file that I serve from my server when users
request it. Thanks again!

Sorry, as David pointed out I was only working with the SELECT portion
of the query. I tend to break things into smaller subunits and verify
they work before combining. When I worked on your first statements I
realized the SELECT portion was not working, so I started there.

On Fri, Mar 27, 2015 at 1:49 PM, Jan de Visser <jan@de-visser.net
<mailto:jan@de-visser.net>> wrote:

On March 27, 2015 11:38:42 AM David G. Johnston wrote:

On Fri, Mar 27, 2015 at 11:31 AM, Jan de Visser

<jan@de-visser.net <mailto:jan@de-visser.net>> wrote:

On March 27, 2015 01:12:52 PM Eli Murray wrote:

ERROR: syntax error at or near "json_build_object"
LINE 1: insert into json(data) json_build_object(SELECT

DISTINCT dep...

You may want to review the syntax of the INSERT command, i.e.

this doesn't

have anything to do with JSON:

INSERT INTO foo ( bar, baz, quux, froz ) VALUES (NULL, 12,

'box', now())

​or..

INSERT INTO json(data) SELECT json_build_object(SELECT DISTINCT

dep...)

​David J.

Serves me right for bringing the snark without properly reading the
OP :-)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Senior Web Developer at The Daily Illini
ejmurra2@illinimedia.com <mailto:ejmurra2@illinimedia.com>
(815) 985-8760

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general