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.

Mark Longair · Accepted Answer · 2022-06-15 15:41:38Z

182

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, 2022 at 15:41

Mark Longair

472k77 gold badges427 silver badges330 bronze badges

answered Feb 14, 2017 at 18:23

Evan Carroll

1

Sign up to request clarification or add additional context in comments.

11 Comments

user916367 Over a year ago

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

Evan Carroll Over a year ago

@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.

Cody Canning Over a year ago

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

Cody Canning Over a year ago

better yet: SET jsondata = jsondata - 'newString'

Maxim Mandrik Over a year ago

Current answer and documentation helped.

|

371273 · Accepted Answer · 2017-05-02 21:41:54Z

46

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 2, 2017 at 21:41

371273

5,46811 gold badges51 silver badges69 bronze badges

5 Comments

Evan Carroll Over a year ago

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

ZiiMakc Over a year ago

@EvanCarroll can you add an example please?

Håken Lid Over a year ago

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

Greg Hornby Over a year ago

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

user299709 Over a year ago

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

Rahul Kumar · Accepted Answer · 2021-10-19 12:06:34Z

14

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;

answered Oct 19, 2021 at 12:06

Rahul Kumar

8598 silver badges8 bronze badges

Comments

IvanTrofimov · Accepted Answer · 2023-04-13 16:44:53Z

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, 2023 at 16:44

answered Apr 13, 2023 at 16:39

IvanTrofimov

1671 silver badge8 bronze badges

Comments

Mahmud Abubakar · Accepted Answer · 2025-04-05 14:36:36Z

-1

remove syntax in MySQL when working with int value in the array

UPDATE jsontesting
SET jsondata= (
    SELECT JSON_ARRAYAGG(value)
    FROM JSON_TABLE(
        jsondata,
        '$[*]' COLUMNS (
            value INT PATH '$'
        )
    ) AS jt
    WHERE value != 7

answered Apr 5 at 14:36

Mahmud Abubakar

1

Collectives™ on Stack Overflow

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

For versions less than 9.5 see this question

5 Answers 5

11 Comments

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

11 Comments

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related