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.

score 155 · Accepted Answer · edited Jun 15 '22 at 15:41

155

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;

edited Jun 15 '22 at 15:41

Mark Longair

446,582
72
411
327

answered Feb 14 '17 at 18:23

Evan Carroll

78,363
46
261
468

1

What's the performance like on this for appending to already very large arrays? Does postgres need to load the entirety of the row? – Sep 13 '17 at 13:07
Any time you update a column, PostgreSQL needs to update the entire the row. – Evan Carroll Sep 13 '17 at 15:08
could you give me a source for that? I'd just like to read a bit more into it. – Sep 13 '17 at 15:56
1

@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. – Evan Carroll Sep 13 '17 at 16:46
1

There's a typo. `SET jsondata = jsondata - "newString"` should be `SET jsondata = jsondata - '"newString"'` – Cody Canning Jun 25 '18 at 20:14
1

better yet: `SET jsondata = jsondata - 'newString'` – Cody Canning Jun 25 '18 at 20:31
@Kostanos: I am letting you know your edit has been rolled back here, in case you wish to discuss it with Evan. – halfer Aug 11 '19 at 10:10
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? – pomo Mar 04 '20 at 11:02
1

Current answer and [documentation](https://postgrespro.com/docs/postgrespro/12/functions-json) helped. – Maxim Mandrik Jun 07 '20 at 12:44
It seems, that `|| '["newString"]'::jsonb)` could as well be `|| '"newString"'::jsonb)`, which is pretty helpful in some languages. Tested on postgres 13.3 – Alveona Aug 09 '21 at 15:51
Postgres13: `SET jsondata = jsondata - '"newString"'` will fail. `SET jsondata = jsondata - 'newString'` works. – jian Oct 04 '21 at 11:18

score 42 · Answer 2 · answered May 02 '17 at 21:41

42

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;

answered May 02 '17 at 21:41

winduptoy

5,366
11
49
67

4

I would either use coalesce, or write it into the conditional. – Evan Carroll May 02 '17 at 21:47
@EvanCarroll can you add an example please? – ZiiMakc Mar 20 '19 at 15:12
2

@RTW: postgresql etc. has a built in function called [coalesce](https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL) that does the same as this case expression. – Håken Lid Mar 27 '19 at 17:45
24

@RTW `SET jsondata = COALESCE(jsondata, '[]'::JSONB) || '["newString"]'::JSONB` – Greg Hornby Oct 02 '19 at 03:41
6

this should be the top answer....I was wondering why it was still null and I suspected this. have an upvote sir. – user299709 Nov 28 '19 at 00:08

score 10 · Answer 3 · answered Oct 19 '21 at 12:06

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;

IvanTrofimov · Answer 4 · 2023-04-13T16:44:53.530

0

- 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'

edited Apr 13 '23 at 16:44

answered Apr 13 '23 at 16:39

IvanTrofimov

167
1
7

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

For versions less than 9.5 see this question

4 Answers4

Linked