I've been looking for solution for days now but no success yet. I tried different code snippets from other's answers but somehow nothing worked so far.

I would like to remove a JSON data from my JSONB column inside PostgreSQL.

I am trying to delete the next syntax:

 {
  "name": "vmi2",
  "path": "efefe\\feff\\\fefefe",
  "type": "Gym"
 }

This is what my JSON looks like in PostgreSQL:

[
 {
  "name": "vmi2",
  "path": "efefe\\feff\\\fefefe",
  "type": "Gym"
 },
 {
  "name": "vmi",
  "path": "efefe\\feff\\\fefefe",
  "type": "Gym"
 },
 {
  "name": "vmi3",
  "path": "efefe\\feff\\\fefefe",
  "type": "Gym"
 }
]

This is how I append new data into it (Works):

UPDATE my_users 
SET json_workouts = 
COALESCE(json_workouts, '[]'::JSONB) || '{"name": "vmi3", "path": 
"efefe\\feff\\\fefefe", "type": "Gym"}' 
WHERE username = 'c';

And these are the 2 queries I tried to remove data. Neither worked, though I have looked up these codes from the same article( removing and appending queries too):

First 'deleting' query (Doesn't work):

DELETE FROM my_users
WHERE json_workouts ->> 'name' = 'vmi2';

Second 'deleting' query. (Doesn't work)

UPDATE my_users
SET json_workouts = json_workouts - 'name'
WHERE username = 'c';

CodePudding user response：

I understand that you want to filter out one (or more) elements from the JSON array, based on the name property of each element.

One approach unnests the JSON array, filters out unwanted element(s), and then re-aggregates.

Assuming a table like t(id, js), where id is the primary key and js is a jsonb column, you can do:

select t.id, jsonb_agg(j.elt order by j.idx) as new_js
from t
cross join lateral jsonb_array_elements(t.js) with ordinality as j(elt, idx)
where j.elt ->> 'name' != 'vmi2'
group by t.id

Here is a small demo on DB Fiddle; for your sample data, the query returns: