Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+
To add the value use the JSON array append opperator (||
)
UPDATE jsontestingSET jsondata = jsondata || '["newString"]'::jsonbWHERE id = 7;
Removing the value looks like this
UPDATE jsontestingSET jsondata = jsondata - "newString"WHERE id = 7;
Concatenating to a nested field looks like this
UPDATE jsontestingSET jsondata = jsonb_set( jsondata::jsonb, array['nestedfield'], (jsondata->'nestedfield')::jsonb || '["newString"]'::jsonb) WHERE id = 7;
To add to Evan Carroll's answer, you may want to do the following to set the column to an empty array if it is NULL
. The append operator (||
) does nothing if the column is currently NULL
.
UPDATE jsontesting SET jsondata = ( CASE WHEN jsondata IS NULL THEN '[]'::JSONB ELSE jsondata END) || '["newString"]'::JSONB WHERE id = 7;