Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+

Question

For versions less than 9.5 see this question

I have created a table in PostgreSQL using this:

CREATE TEMP TABLE jsontesting AS   SELECT id, jsondata::jsonb FROM ( VALUES     (1, '["abra","value","mango", "apple", "sample"]'),     (2, '["japan","china","india", "russia", "australia"]'),     (3, '["must", "match"]'),     (4, '["abra","value","true", "apple", "sample"]'),     (5, '["abra","false","mango", "apple", "sample"]'),     (6, '["string","value","mango", "apple", "sample"]'),     (7, '["must", "watch"]')   ) AS t(id,jsondata); 

Now what I wanted was to

• add Something like append_to_json_array takes in the actual jsondata which is a json-array and the newString which I have to add to that jsondata array and this function should return the updated json-array.

  UPDATE jsontesting SET jsondata=append_to_json_array(jsondata, 'newString') WHERE id = 7; 

• remove a value from the json data array, one function for removing the value.

I tried to search documentation of PostgreSQL but found nothing there.

Answer 1

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; 

Answer 2

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;