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

Answer 1

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