Remove numeric item from JsonB array

Two assertions:

1. Many Postgres JSON functions and operators target the key in key/value pairs. Strings ("abc" or "33") in JSON arrays are treated like keys without value. But numeric (33 or 123.45) array elements are treated as values.

2. There are currently three variants of the - operator. Two of them apply here. As the recently clarified manual describes (currently /devel):

   Operator
         Description
         Example(s)
   :---------------------
   jsonb - text → jsonb
         Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.
         '{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"}
      '["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]
   ...
   jsonb - integer → jsonb
         Deletes the array element with specified index (negative integers count from the end).
         Throws an error if JSON value is not an array.
         '["a", "b"]'::jsonb - 1 → ["a"]

With the right operand being a numeric literal, Postgres operator type resolution arrives at the later variant.

Unfortunately, we cannot use the former variant to begin with, due to assertion 1.

So we have to use a workaround like:

SELECT jsonb_set(column_name
               , '{values}'
               , (SELECT jsonb_agg(val)
                  FROM   jsonb_array_elements(t.column_name -> 'values') x(val)
                  WHERE  val <> jsonb '33')
                 ) AS column_name
FROM   tbl t;

db<>fiddle here -- with extended test case

Do not cast unnested elements to integer (like another answer suggests).

• Numeric values may not fit integer.
• JSON arrays (unlike Postgres arrays) can hold a mix of element types. So some array elements may be numeric, but others string, etc.
• It's more expensive to cast all array elements (on the left). Just cast the value to replace (on the right).

So this works for any types, not just integer (JSON numeric). Example:

'{"values": ["abc", "22", 33]}') 

Unfortunately, Postgres json operator - only supports string values, as explained in the documentation:

operand: -

right operand type: text

description: Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.

On the other hand, if you pass an integer value as right operand, Postgres considers it the index of the array element that needs to be removed.

An alternative option is to unnest the array with jsonb_array_elements() and a lateral join, filter out the unwanted value, then re-aggregate:

select jsonb_set(column_name, '{values}', new_values) new_column_name
from mytable t
left join lateral (
    select jsonb_agg(val) new_values
    from jsonb_array_elements(t.column_name -> 'values') x(val)
    where val::int <> 33
) x on 1 = 1

Demo on DB Fiddle:

with mytable as (select '{"values": [11, 22, 33]}'::jsonb column_name)
select jsonb_set(column_name, '{values}', new_values) new_column_name
from mytable t
left join lateral (
    select jsonb_agg(val) new_values
    from jsonb_array_elements(t.column_name -> 'values') x(val)
    where val::int <> 33
) x on 1 = 1

| new_column_name      |
| :------------------- |
| {"values": [11, 22]} |