I have jsonb value with a nested JSON array and need remove an element:
{"values": ["11", "22", "33"]}
jsonb_set(column_name, '{values}', ((column_name -> 'values') - '33')) -- WORKS!
I also have a similar jsonb value with numbers, not strings:
{"values": [11, 22, 33]}
jsonb_set(column_name, '{values}', ((column_name -> 'values') - 33)) -- FAILS!
In this case 33 is used as index of the array.
How to remove items from JSON array when those items are numbers?
Two assertions:
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.
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).
integer. numeric, but others string, etc. So this works for any types, not just integer (JSON numeric). Example:
'{"values": ["abc", "22", 33]}')
'33' to jsonb via associated input function. The second processes the numeric literal 33, type resolution arrives at integer, so it is coerced to integer using the associated input function; then functions named to_jsonb with one argument are looked up, one is found and function type resolution accepts it as it takes anyelement, then the function is executed and transforms integer to jsonb. So, yes, the first one is a bit faster and less error prone. Shorter, too. (And clearer IMO.)'33'::jsonb does the same as jsonb '33'. But the latter is one character shorter. :)Unfortunately, Postgres json operator - only supports string values, as explained in the documentation:
operand:
-right operand type:
textdescription: 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
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]} |
Starting from this version it has powerful functionality for working with JSONB using jsonpath syntax. There is a great article written by author of this feature which describes all use cases better than official docs.
Get all array items except 33:
SELECT jsonb_path_query_array(
'{"values": [11, 22, 33]}'::jsonb,
'$.values[*] ? (@ <> 33)'
)
-- [11, 22]
With a combination of jsonb_set it gives exactly what we want:
jsonb_set(column_name, '{values}', jsonb_path_query_array(column_name, '$.values[*] ? (@ <> 33)'))
