Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+
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;
To add the value use the JSON array append opperator (||
)
UPDATE jsontesting
SET jsondata = jsondata || '["newString"]'::jsonb
WHERE id = 7;
Removing the value looks like this
UPDATE jsontesting
SET jsondata = jsondata - 'newString'
WHERE id = 7;
Concatenating to a nested field looks like this
UPDATE jsontesting
SET jsondata = jsonb_set(
jsondata::jsonb,
array['nestedfield'],
(jsondata->'nestedfield')::jsonb || '["newString"]'::jsonb)
WHERE id = 7;