0

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"}]
dman
  • 10,406
  • 18
  • 102
  • 201
  • 2
    The top level element in `images` is an _array_ not a JSON object. The `-` operator does not work with arrays. –  Apr 20 '17 at 06:03

1 Answers1

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)
mccalljt
  • 786
  • 3
  • 14
  • 1
    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 – dman Apr 20 '17 at 07:03
  • 2
    This looks like a viable strategy: http://stackoverflow.com/questions/38996212/postgresql-9-5-jsonb-set-getting-index-of-json-array – mccalljt Apr 20 '17 at 22:47