Update/Add Array Value PostgreSQL

user_2859698 0
Viewed 32

My table:

CREATE TABLE public.invoice (
    id bigint NOT NULL,
    json_data jsonb NOT NULL
);

My Data:

INSERT INTO public.invoice (id,json_data) VALUES
     (1,'{"Id": 1, "Items": ["2", "3", "1"], "Invoice": "CR000012"}');

Todo List:

  1. Need to add to "Items" a new value i.e "5". (output of items ["2", "3", "1","5"])

  2. Need to update items value 2 to 9. (output of items ["9", "3", "1","5"])

I have tried below but this will replace the array values not update or add

UPDATE invoice  SET json_data = jsonb_set(json_data, '{invoice }', '"4"') where Id ='1'
postgresql arrays
1 Answers

I recommend you to use this aproach, you should point element with index of array.

In your case, your code should look something like this,

1. Add to Items a new value i.e "5

UPDATE invoice SET json_data = jsonb_set(json_data, {Items,0}, json_data->'Items' || '"5"', TRUE) where Id =1

2. Update Items value 2 to 9.

UPDATE invoice SET json_data = jsonb_set(json_data, {Items,0}, '"9"') where Id =1

You can check PostgreSQL, JSON Functions and Operators from here.

user_59551380
Related