Convert Python list of dicts into Postgresql array of json

Question

I am trying to insert a Python(2.7) list of jsonb elements into a Postgresql(9.4) table with a column of datatype: jsonb[].

Here's some code:

import json
anArray = [{"name":"Joe","age":51,"yob":1964,"gender":"male"},{"name":"George","age":41,"dob":1974,"gender":"male"},{"name":"Nick","age":31,"dob":1984,"gender":"male"}]
myArray = []
#here's what I have so far: 
for e in anArray:
    myArray.append(json.dumps(e))
#this gives me
myArray = ['{"name":"Joe","age":51,"yob":1964,"gender":"male"}','{"name":"George","age":41,"dob":1974,"gender":"male"}','{"name":"Nick","age":31,"dob":1984,"gender":"male"}']
#insert commands
insert_sql = "INSERT INTO my_table (data) VALUES (%s);"
insert_data = (myArray, )
cursor.execute(insert_sql, insert_data)

Now when I try to insert myArray, psycopg2 gives me an error

psycopg2.ProgrammingError: column "data" is of type jsonb[] but expression is of type text[]

I'm not quite sure what the correct syntax is to insert this data into the table. Any help/pointers will be appreciated.

Solution Thanks to piro, it was a quick solution.

insert_sql = "INSERT INTO my_table (columns) VALUES (%s::jsonb[]);"
insert_data = (myArray, )
cursor.execute(insert_sql, insert_data)

Alex · Accepted Answer · 2017-03-06 11:17:32Z

7

This is not a psycopg error: it is a PostgreSQL error psycopg is just relying.

The error seems suggesting there is no implicit text[]->jsonb[] cast so you need to add a manual one:

INSERT INTO my_table (columns) VALUES (%s::jsonb[]);

edited Mar 6, 2017 at 11:17

Alex

6,1071 gold badge36 silver badges53 bronze badges

answered Jul 27, 2015 at 15:46

piro

14k6 gold badges39 silver badges40 bronze badges

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

2 Comments

JetJet13 Over a year ago

Well then... That worked like a charm. Thanks so much @piro !

Alex Over a year ago

I tried this, but it creates an empty json that cannot be inserted: '''{}''' DETAIL: Array value must start with "{" or dimension information.

Clodoaldo Neto · Accepted Answer · 2017-03-06 12:22:52Z

6

Use Psycopg JSON adaptation

from psycopg2.extras import Json

data = [
    {"name":"Joe","age":51,"yob":1964,"gender":"male"},
    {"name":"George","age":41,"dob":1974,"gender":"male"},
    {"name":"Nick","age":31,"dob":1984,"gender":"male"}
]

query = '''
    insert into t (j)
    select array_agg(j)
    from jsonb_array_elements(%s) s(j)
'''

cursor.execute(query, (Json(data),))

jsonb_array_elements will return a set of jsonb which will be turned into an array of jsonb by array_agg

edited Mar 6, 2017 at 12:22

answered Mar 6, 2017 at 11:54

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

dave4jr Over a year ago

This is the best solution.

Collectives™ on Stack Overflow

Convert Python list of dicts into Postgresql array of json

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related