Update jsonb column to replace specific element

Question

I want to update a jsonb column to replace an element of array inside it e.g Tv to TV. I am using postgresql 12.4 with 5M rows on that table.

Top 10 Rows:

categories
["Air Conditioner", "Kitchen", "Bedding/Linens", "Parking", "Tv", "Child Friendly", "Internet", "Laundry"]
["Hot Tub", "Air Conditioner", "Kitchen", "Bedding/Linens", "Tv", "Wheelchair Accessible", "Pool", "Internet", "Laundry"]
["Hot Tub", "Air Conditioner", "Kitchen", "Bedding/Linens", "Balcony/Terrace", "Parking", "Tv", "Wheelchair Accessible", "Child Friendly", "Pool", "Internet", "Laundry"]
["Balcony/Terrace", "Kitchen", "Bedding/Linens", "Tv", "Wheelchair Accessible", "Internet", "Laundry"]
["Air Conditioner", "Kitchen", "Bedding/Linens", "Parking", "Tv", "Pool", "Internet", "Laundry"]
["Hot Tub", "Air Conditioner", "Kitchen", "Bedding/Linens", "Parking", "Balcony/Terrace", "Tv", "Child Friendly", "Internet", "Pet Friendly", "Laundry"]
["Hot Tub", "Air Conditioner", "Kitchen", "Bedding/Linens", "Balcony/Terrace", "Parking", "Tv", "Wheelchair Accessible", "Pool", "Internet", "Laundry"]
["Air Conditioner", "Kitchen", "Bedding/Linens", "Tv", "Wheelchair Accessible", "Internet", "Laundry"]
["Kitchen", "Bedding/Linens", "Parking", "Tv", "Internet", "Laundry"]
["Air Conditioner", "Kitchen", "Bedding/Linens", "Parking", "Tv", "Wheelchair Accessible", "Internet", "Laundry"]

What is working:

UPDATE test_jsonb_update 
SET amenity_categories = replace(amenity_categories::TEXT,'"Tv"','"TV"')::jsonb 
WHERE id < 10;

What I want:

I want to know other alternatives to update that element on jsonb column. I've seen this example https://stackoverflow.com/a/35349699/1138192 and I want to use something like jsonb_set on my case also. Let me know other alternatives and best practices as I have 5M records to do update that

Do you want to keep the order? – S-Man Feb 08 '21 at 10:16 — S-Man, Feb 08 '21 at 10:16
not necessarily, If I can then it will be good – A l w a y s S u n n y Feb 08 '21 at 11:24 — A l w a y s S u n n y, Feb 08 '21 at 11:24

S-Man · Accepted Answer · 2021-02-08T10:16:01.223

IMHO is your way the most efficient. In any other ways (if you want to keep the order!) you need to expand the array elements into separate rows (either for retrieving the index of the old "Tv" to set the new "TV" to the same position using jsonb_set or for string search/replace) and reaggregate all...

demos:db<>fiddle

SELECT
    id,
    jsonb_agg(CASE WHEN elem = 'Tv' THEN 'TV' ELSE elem END) as new_array
FROM mytable,
    jsonb_array_elements_text(mydata) as elem
GROUP BY id

If you really want to use jsonb_set (which make no sense here, I believe) you could do something like:

SELECT
    jsonb_set(mydata, ARRAY[s.elem_id]::text[], '"TV"') - 'Tv'
FROM mytable t
JOIN (
    SELECT
        id,
        elem_id
    FROM mytable,
        jsonb_array_elements_text(mydata) WITH ORDINALITY as elem(value, elem_id)
    WHERE elem.value = 'Tv'
)s ON s.id = t.id

Expand the array elements and show their indexes with WITH ORDINALITY
Now you can use these indexes to place the new "TV" element to the right place with jsonb_set()
Afterwards you need to remove the old "Tv" element from the updated array.

Finally do the update with:

UPDATE mytable t
SET mydata = s.my_array
FROM (
    -- query
) s
WHERE t.id = s.id

score 1 · Answer 2 · answered Feb 08 '21 at 09:59

1

The ordering on your json array doesn't seem fixed, so this should do the job and avoid any unnesting.

UPDATE ...
SET amenity_categories = (amenity_categories - 'Tv') || jsonb '["TV"]'
WHERE amenity_categories ? 'Tv';

answered Feb 08 '21 at 09:59

Richard Huxton

21,516
3
39
51

This only works if the order does not matters – S-Man Feb 08 '21 at 10:15
Yep. Otherwise you need to unnest `WITH ORDINALITY` as in your example. Or write a custom function. – Richard Huxton Feb 08 '21 at 11:28

Update jsonb column to replace specific element

2 Answers2