Delete a value from jsonb array data having no key in postgresql-CodePudding

Table structure is:

CREATE TABLE mine_check.meta
(
  sl_no bigserial NOT NULL,
  tags jsonb NOT NULL DEFAULT '[]'::jsonb
);

Table looks like

    sl.no    tags
    1        [120,450]
    2        [120]
    3        [450,980,120]
    4        [650]

I need to delete 120 from the tags column - having no key

I tried reading many places - there they had key to update or delete.

How should I progress ?

CodePudding user response：

I am afraid that it has to be done the hard way - unnest the JSONB array, select and filter from it and aggregate back into a JSONB array.

select sl_no, 
(
  select jsonb_agg(e::integer) 
  from jsonb_array_elements_text(tags) e 
  where e <> 120::text
) tags
from mine_check.meta;