Re: Building JSON objects - Mailing list pgsql-general
| From | Eli Murray |
|---|---|
| Subject | Re: Building JSON objects |
| Date | |
| Msg-id | CAHReO_u=sga4-7=D+dwJHoo6FH5_2aavurOJMiwLjGGpB8i1cg@mail.gmail.com Whole thread Raw |
| In response to | Re: Building JSON objects (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Building JSON objects
(Jan de Visser <jan@de-visser.net>)
Re: Building JSON objects (Adrian Klaver <adrian.klaver@aklaver.com>) Re: Building JSON objects (Szymon Guz <mabewlun@gmail.com>) Re: Building JSON objects (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | 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 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
pgsql-general by date: