How to push to an array inside of a json object in a jsonb column in postgresql?

@rollin 

To push to an array inside of a JSON object in a JSONB column in PostgreSQL, you can use the jsonb_set function combined with the || operator to concatenate the existing array with the new element. Here's an example:

Suppose you have a table called my_table with a column called json_data of type JSONB that stores JSON objects. You want to push a new element to an array inside of the JSON object in the json_data column.

1
2
3
4

UPDATE my_table
SET json_data = jsonb_set(json_data, '{key_inside_object}', 
                          (json_data->'key_inside_object' || '["new_element"]')::jsonb)
WHERE condition;

In the above query:

• jsonb_set is used to update the JSONB column by setting the value at a specific path.
• '{key_inside_object}' is the path to access the array inside the JSON object.
• json_data->'key_inside_object' accesses the array inside the JSON object.
• || '["new_element"]' concatenates the existing array with the new element 'new_element'.
• ::jsonb is used to cast the concatenated result back to a JSONB type.

Replace my_table, key_inside_object, and condition with the actual table name, key path, and condition for the update operation. This query will add the new element to the array inside the JSON object stored in the json_data column.