CODEWITHSUNDEEP
postgresqljsonb
Rogier Lommers
Rogier Lommers

Reputation: 2443

How to delete object in jsonb having a certain value

I have a jsonb type with the following example data:

[
  {
    "name": "test1_name",
    "lastname": "test1_lastname"
  },
  {
    "name": "test2_name",
    "lastname": "test2_lastname",
    "order": 14
  }
]

Now I'm trying to find a way to delete all documents inside the array where the order field is NOT NULL. I really don't have a clue how to do this. So basically in words I want this:

"delete all objects inside the arrays
where order-value is not null"

I really don't know how to start with this; since I don't know the array-index of the document I eventually want to delete. Any suggestions?

Upvotes: 0

Views: 59

Answers (2)

syllabix
syllabix

Reputation: 2295

If I understand your question correctly - and making an additional assumption you have a table that isn't just a single column of a jsonb array but something that looks like this called my_table

| id (primary key) | user_list (jsonb array) |

Using a with statement - you could do something like the following:

WITH user_update AS (
SELECT
    id, users - (index::integer - 1) as new_list
FROM my_table,
    jsonb_array_elements(user_list) WITH ORDINALITY elems(data, index)
WHERE
    elems.data -> 'order' IS NOT NULL
)
UPDATE my_table SET user_list = user_update.new_list from user_update where my_table.id = user_update.id;

Upvotes: 1

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

SELECT
    jsonb_agg(elems.data ORDER BY elems.index)
FROM mytable,
    jsonb_array_elements(mydata) WITH ORDINALITY elems(data, index)
WHERE 
    elems.data -> 'order' IS NULL
  1. Expand the array into one record per element (use the WITH ORDINALITY clause to get the indexes to store the right order)
  2. Filter the relevant data
  3. Reaggregate the remaining elements using the index.

Upvotes: 0

Related Questions