Store Array of Json Objects In Postgresql Using Python

Question

I am currently trying to store two lists of json objects into my postgresql database using psycopg2 without success.

Here's a snippet of what I am trying to do...

SQL_INSERT_QUERY = """INSERT INTO table (
                              x,
                              json_list_one,
                              json_list_two
                            ) VALUES (%s, %s, %s)"""

def insert_stuff(id, json_list_one, json_list_two):
values = (
    id,
    map(psycopg2.extras.Json, json_list_one),
    map(psycopg2.extras.Json, json_list_two)
)
conn = get_connection_pool().getconn()
try:
    cur = conn.cursor()
    cur.execute(SQL_INSERT_QUERY, values)
    conn.commit()
    cur.close()
finally:
    get_connection_pool().putconn(conn)

This current implementation is resulting in this error...

An exception of type ProgrammingError occured. Arguments:
('column "json_list_one" is of type json[] but expression is of type text[]\nLINE 5: ...) VALUES (\'9527d790-31dc-46fa-b683-c6fc9807c2a4\', ARRAY[\'{"h...\n                                                             ^\nHINT:  You will need to rewrite or cast the expression.\n',)

Does anyone know how I am supposed to insert an array of json objects in PostgreSQL?

Thank you.

stackoverflow.com/a/45150668/3598837 this answer solved it for me — bibangamba
– bibangamba, Commented Oct 2, 2018 at 13:56

Roman Tkachuk · Accepted Answer · 2017-02-05 06:45:13Z

2

You need cast value to son --

SQL_INSERT_QUERY = """INSERT INTO table (
                              x,
                              json_list_one,
                              json_list_two
                            ) VALUES (%s, %s::json[], %s::json[])"""

And if you use PostgreSQL 9.5 or later you need querying over json fields is better to change json to jsonb. Jsonb allow to create GIN index than allow increase performance of your query.

answered Feb 5, 2017 at 6:45

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

This returns DETAIL: Array value must start with "{" or dimension information . what could be wrong?

Roman Tkachuk Over a year ago

By default postgres cast unknown data as text. As you can see in error 'column "json_list_one" is of type json[] but expression is of type text[] than error in bad type for field - text[] instead json[].

bibangamba · Accepted Answer · 2018-10-02 14:02:19Z

0

from here: https://stackoverflow.com/a/45150668/3598837

from psycopg2.extras import Json

cur = conn.cursor()

cur.execute('INSERT into some_table (uuid, dict) values (%s, %s)',
    ['testName', Json([{'name':'test','number':'444-444-4444'}, {'name':'test','number':'555-555-5555'}])])
#the rest of it

answered Oct 2, 2018 at 14:02

bibangamba

1,4931 gold badge19 silver badges24 bronze badges

Comments

Jessie · Accepted Answer · 2017-02-05 04:28:43Z

-4

You need to use the json library and call json.dumps(dict_here). Json is not a supported type in Postgres. You use json.dumps to turn a dictionary into a json string, and json.loads to turn it back into a dictionary.

answered Feb 5, 2017 at 4:28

Jessie

2,5251 gold badge21 silver badges34 bronze badges

1 Comment

Dylan Over a year ago

Actually PosgreSQL does support Json type (since 9.3?). I have stored Json (not as an array) in other tables.

Collectives™ on Stack Overflow

Store Array of Json Objects In Postgresql Using Python

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related