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

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

python-3.xpostgresqlpsycopg2jsonb-array-elements


Solution 1:[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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bjarni Ragnarsson

Related Questions