psycopg2 - Append a value to a jsonarray in a json column at a specific key

Question

How to add an element in a jsonarray object stored in a dict to a specific key and row of this postresql table with psycopg2 python lib :

To illustrate, go from this :

|           json_column          | code |
|--------------------------------|------|
|{"key" : ["value1", "value2"]}  |  125 |

to that :

|                json_column               | code |
|------------------------------------------|------|
|{"key" : ["value1", "value2", "value3"]}  |  125 |

I have tried this query :

cursor = connection.cursor()

postgres_insert_query =  
      """ UPDATE table
          SET json_column = jsonb_set(json_column, '{"key"}', json_column->'key' || 
             '["value3"]'::jsonb)::json
          WHERE code = 125 """

cursor.execute(postgres_insert_query)

It returns the following error : invalid input syntax for the json type

Bjarni Ragnarsson · Accepted Answer · 2022-04-28 08:21:19Z

0

This is probably because the column is json but the jsonb_set operates on jsonb object. Try this:

UPDATE t
SET json_column = jsonb_set(json_column::jsonb, '{"key"}', (json_column->'key')::jsonb || '["value3"]')::json
WHERE code = 125 ;

Best regards, Bjarni

answered Apr 28, 2022 at 8:21

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

psycopg2 - Append a value to a jsonarray in a json column at a specific key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related