How to delete multiple values in JSONB array Postgresql array object

1
votes

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?

3
jsonpostgresqlsql-updatejsonb

3 Answers

1
votes

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

Demo

0
votes

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

0
votes

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 = ...;

Online example