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

Evan Carroll picture Evan Carroll · Feb 14, 2017 · Viewed 30.7k times · Source

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

Evan Carroll picture Evan Carroll · Feb 14, 2017

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;