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

Question

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 ?

Stefanov.sm · Accepted Answer · 2022-05-26 17:45:22Z

1

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;

answered May 26, 2022 at 17:45

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I'm using 9.4 postgress version - jsonb_agg is not supported in that - is there any other way?

Yes, under PG 9.4 you may replace jsonb_agg(e::integer) with json_agg(e::integer)::jsonb. TIme to update though.