Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+
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;
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;
I was facing similar issue to append to an existing json data in postgres with new key-value pair.
I was able to fix this using append operator ||
as follows:
UPDATE jsontesting
SET jsondata = jsondata::jsonb || '{"add_new_data": true}'
WHERE id = 7;