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.

Whitewash

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;

Whitewash

What's the performance like on this for appending to already very large arrays? Does postgres need to load the entirety of the row? – Solly

Any time you update a column, PostgreSQL needs to update the entire the row. – Whitewash

could you give me a source for that? I'd just like to read a bit more into it. – Solly

@Rollo anything MVCC. It's how they all work. It solves the isolation problem. When you update a row, that row can not be visible to any transaction prior to your commit, but it must be visible to your own transaction. So you write that row on the table as a dead row. When you commit you make it live. Until you commit, other transactions view the row in it's previous state. – Whitewash

There's a typo. SET jsondata = jsondata - "newString" should be SET jsondata = jsondata - '"newString"' – Honesty

better yet: SET jsondata = jsondata - 'newString' – Honesty

@Kostanos: I am letting you know your edit has been rolled back here, in case you wish to discuss it with Evan. – Anchorage

The 3rd example doesn't quite work when using the 4th argument to create the nested array if it doesn't exists. In fact the result is that the column gets set to null. Can you please add a working example for this case? – Shashaban

Current answer and documentation helped. – Specify

It seems, that || '["newString"]'::jsonb) could as well be || '"newString"'::jsonb), which is pretty helpful in some languages. Tested on postgres 13.3 – Feria

Postgres13: SET jsondata = jsondata - '"newString"' will fail. SET jsondata = jsondata - 'newString' works. – Abaddon

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;

Adamsun

I would either use coalesce, or write it into the conditional. – Whitewash

@EvanCarroll can you add an example please? – Ginetteginevra

@RTW: postgresql etc. has a built in function called coalesce that does the same as this case expression. – Pontiff

@RTW SET jsondata = COALESCE(jsondata, '[]'::JSONB) || '["newString"]'::JSONB – Roldan

this should be the top answer....I was wondering why it was still null and I suspected this. have an upvote sir. – Sahara

Answer 3

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;

Mohammadmohammed

Answer 4

- replace (update) a value from the json data array (replace 'oldString' with 'newString'):

UPDATE jsontesting
SET jsondata = (jsondata || '["newString"]'::jsonb)::jsonb - 'oldString'
WHERE jsondata ? 'oldString'

Scolopendrid