I have a JSONB array below
[
{
"name": "test",
"age": "21",
"phone": "6589",
"town": "54"
},
{
"name": "test12",
"age": "67",
"phone": "6546",
"town": "54"
},
{
"name": "test123",
"age": "21",
"phone": "6589",
"town": "54"
},
{
"name": "test125",
"age": "67",
"phone": "6546",
"town": "54"
}
]
Now I want to delete the object if the name is test or test125. How to delete multiple or single values in JSONB array?
An update statement including a subquery, which eleminates the unwanted elements with NOT IN operator and aggregates the rest by using jsonb_agg() function, would find out this operation :
Choose this :
1. UPDATE tab
SET jsdata = t.js_new
FROM
(
SELECT jsonb_agg( (jsdata ->> ( idx-1 )::int)::jsonb ) AS js_new
FROM tab
CROSS JOIN jsonb_array_elements(jsdata)
WITH ORDINALITY arr(j,idx)
WHERE j->>'name' NOT IN ('test','test125')
) t
or this one :
2. WITH t AS (
SELECT jsonb_agg( (jsdata ->> ( idx-1 )::int)::jsonb ) AS js_new
FROM tab
CROSS JOIN jsonb_array_elements(jsdata)
WITH ORDINALITY arr(j,idx)
WHERE j->>'name' NOT IN ('test','test125')
)
UPDATE tab
SET jsdata = js_new
FROM t
If you have the Postgres 12, you can use jsonb_path_query_array function to filter the jsonb here is the sample for your question:
with t (j) as ( values ('[
{"name":"test","age":"21","phone":"6589","town":"54"},
{"name":"test12","age":"67","phone":"6546","town":"54"},
{"name":"test123","age":"21","phone":"6589","town":"54"},
{"name":"test125","age":"67","phone":"6546","town":"54"}
]'::jsonb) )
select jsonb_path_query_array(j,
'$[*] ? (@.name != "test" && @.name != "test125")')
from t;
more info on https://www.postgresql.org/docs/12/functions-json.html
I would create a function that does that:
create function remove_array_elements(p_data jsonb, p_key text, p_value text[])
returns jsonb
as
$$
select jsonb_agg(e order by idx)
from jsonb_array_elements(p_data) with ordinality as t(e,idx)
where t.e ->> p_key <> ALL (p_value) ;
$$
language sql
immutable;
Then you can use it like this:
update the_table
set the_column = remove_array_elements(the_column, 'name', array['test', 'test125'])
where id = ...;