In Postgres - How to Remove an Element from JSONB Array (JSONB[]) by Identifier/value

Question

In a Postgresql (13+), a users table has a JSONB[] column (notifications) containing user notification. Notifications are populated by a simple dictionary, resulting in the following array:

[
  { 
    "notification_id": "c20da296", # abbreviating UUID4
    "level": "info",
    "timestamp": ...,
    "content": ...
  },

  .. more notifications ..
]

When a user dismisses a notification, i'd like to remove it from the array, using the notification's id (while leaving the other notifications). Many resources show how to deal with a JSONB nested structure (e.g., here) or across multiple users (e.g., here). Is there a way to achieve this, maybe using the - operator in some way? I'm expecting something along the following:

UPDATE users SET notifications - `{"notification_id": "c20da296"}` 
WHERE user_id = "<user_uuid4>";

note: the user's identifier is known, there is no need to traverse all users to select for the relevant notification_id.

Looks like someone else asked a question similar to this before. They said that they were able to figure out how to do it for just one row and needed help doing it for multiple rows. The accepted answer includes an example showing it done for either one row or multiple rows: stackoverflow.com/questions/40285583/… — Matt Welke
– Matt Welke, Commented Sep 21, 2021 at 0:13
@Matt, see in original post - in the question you're linking to the column is structured ('name', 'type', 'attributes'), under properties column. My column is an array, not a dictionary. — GG_Python
– GG_Python, Commented Sep 21, 2021 at 0:21

AlexElin · Accepted Answer · 2021-09-25 09:24:02Z

0

Use jsonb_path_query_array function with a "not equal" condition in the path.
It returns all JSON items returned by the JSON path for the specified JSON value, as a JSON array

Here's an example

UPDATE users 
SET notifications = jsonb_path_query_array(notifications, '$ ? (@.notification_id != "<notification_id_for_removal>")')
WHERE user_id = "<user_uuid4>";

answered Sep 25, 2021 at 9:24

AlexElin

1,7991 gold badge20 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

In Postgres - How to Remove an Element from JSONB Array (JSONB[]) by Identifier/value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related