Home > mailing lists

Thread: Building JSON objects

Building JSON objects

From

Eli Murray

Date:

27 March 2015, 20:40:16

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.

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?

Senior Web Developer at The Daily Illini

ejmurra2@illinimedia.com

(815) 985-8760

Re: Building JSON objects

From

Adrian Klaver

Date:

27 March 2015, 20:48:05

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

Re: Building JSON objects

From

Eli Murray

Date:

27 March 2015, 21:13:01

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

Re: Building JSON objects

From

Jan de Visser

Date:

27 March 2015, 21:31:11

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())

Re: Building JSON objects

From

Adrian Klaver

Date:

27 March 2015, 21:36:55

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

Re: Building JSON objects

From

"David G. Johnston"

Date:

27 March 2015, 21:38:48

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.

Re: Building JSON objects

From

Szymon Guz

Date:

27 March 2015, 21:42:17

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

insert into json(data) SELECT row_to_json...

regards,

Szymon

Re: Building JSON objects

From

Adrian Klaver

Date:

27 March 2015, 21:51:26

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

Re: Building JSON objects

From

Jan de Visser

Date:

27 March 2015, 21:51:48

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 :-)

Re: Building JSON objects

From

Eli Murray

Date:

27 March 2015, 22:30:19

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

Re: Building JSON objects

From

"David G. Johnston"

Date:

27 March 2015, 23:07:57

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.

Re: Building JSON objects

From

Adrian Klaver

Date:

28 March 2015, 00:23:19

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