Postgres operator "-" is not removing text element from jsonb array

Question

images column is type jsonb.

Reading Postgres docs on the - operator for text it states:

Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.

However, I am not having success. After I run my update I see the element in the array still exists. Is this because it is the only element in the array? If so, how would I deal with such a situation?

dev_dolphin_db=#  update listings set images = images - 'filename' where listings.id = 2;
UPDATE 1

id               | 2
created_at       | 2017-04-20 04:19:41.009022+00
posted_by        | 101090922
images           | [{"review": "/9j/4AAQSC1LnS//2Q==", "filename": "3/2/image-3-2-1492661981049.jpeg"}]

The top level element in images is an array not a JSON object. The - operator does not work with arrays. — user330315
– user330315, Commented Apr 20, 2017 at 6:03

mccalljt · Accepted Answer · 2017-04-20 06:08:50Z

2

If the hash is consistently in the 0th index of the jsonb array, you can delete it with

dev_dolphin_db=#  update listings set images = images #- '{0,filename}' where listings.id = 2;

This uses the #- operator which postgres describes as:

Delete the field or element with specified path (for JSON arrays, negative integers count from the end)

answered Apr 20, 2017 at 6:08

mccalljt

7963 silver badges14 bronze badges

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

2 Comments

dman Over a year ago

It won't always be the 0th index. Any idea how I can find out which index? I took another look at the manual and didn't see any jsonb function or operator to return a index

mccalljt Over a year ago

This looks like a viable strategy: stackoverflow.com/questions/38996212/…

Collectives™ on Stack Overflow

Postgres operator "-" is not removing text element from jsonb array

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related