inserting array of JSON objects

Question

I am using a stored procedure accepting a parameter of type JSON[]. Assuming a list similar to listJson = [{"value":"v1"}, {"value" : "v2"}], I would like to invoke the following method of psycopg2:

cursor.execute("call my_stored_procedure(%XXX)",(listJson,))

but I don't know how to replace %XXX to make the placeholder compatible with the list. I unsuccessfully tried %s::json[] and %s::jsonb[]. If it is not possible to use the method above, I would appreciate if somebody could point me to an other strategy to perform the call. Thanks in advance.

If your question was answered please mark it as solved.

Ionut Ticus
– Ionut Ticus

2020-04-21 15:37:51 +00:00
Commented Apr 21, 2020 at 15:37 — Ionut Ticus
– Ionut Ticus, Commented Apr 21, 2020 at 15:37

Ionut Ticus · Accepted Answer · 2020-04-10 08:29:30Z

1

What about using psycopg2.extras.Json:

cursor.execute("CALL my_stored(%s)",(Json(list),))

Also, try to use better variable names such as values instead of list because that's a built-in type.

answered Apr 10, 2020 at 8:29

Ionut Ticus

2,7992 gold badges22 silver badges28 bronze badges

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

1 Comment

Abelisto Over a year ago

According to the question it should be array of JSONs, not JSON array.

Abelisto · Accepted Answer · 2020-04-10 02:05:10Z

0

import psycopg2, json
...
list = [{"value":"v1"}, {"value" : "v2"}]
listJson = [json.dumps(x) for x in list]
cursor.execute("call my_stored_procedure(%s::json[])",(listJson,))

answered Apr 10, 2020 at 2:05

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Comments

jjanes · Accepted Answer · 2020-04-10 01:37:22Z

0

You don't have any JSON to start with, you just have a list of dict. Is that what you want to have in python?

>>> listJson = [{"value":"v1"}, {"value" : "v2"}]
>>> type(listJson)
<type 'list'>
>>> type(listJson[0])
<type 'dict'>
>>> type(listJson[1])
<type 'dict'>

You can make a list of strings which each contain valid JSON syntax, and call with that:

listJson = ['{"value":"v1"}', '{"value" : "v2"}']
cursor.execute("CALL my_stored(%s::json[])",(listJson,))

answered Apr 10, 2020 at 1:37

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

inserting array of JSON objects

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related