UPDATE using jsonb_delete doesn't remove the target keys

Question

I'm learning the jsonb data type in PostgreSQL. I executed the following statements:

CREATE TABLE my_table (
 id SERIAL PRIMARY KEY,
 data JSONB
);
INSERT INTO my_table (data)
VALUES ('{"name": "John", "age": 30, "address": {"city": "New York", "state": "NY"}}');

Then:

UPDATE my_table SET data = jsonb_delete(data, '{address, state}') 
 WHERE id = 1
 RETURNING *;

UPDATE 1

id	data
1	{"age": 30, "name": "John", "address": {"city": "New York", "state": "NY"}}

You can see that address.state field is not removed. Why?

select version();

version
PostgreSQL 18.1 on x86_64-windows, compiled by msvc-19.44.35219, 64-bit

• user7070613: Are you getting jsonb_delete from here jsonb_delete? If so that was a single commit 10 years ago for use in Postgres 9.4, I doubt very much it does anything at this point. For new versions of Postgres use the built in operator as shown in answer below.
• Zegarek: @AdrianKlaver jsonb_delete is the built-in function behind -. It's not listed in the doc but you can see it in pg_operator.oprcode and pg_proc. Dates back to v9.5, so it coincides with the extension you linked, but I didn't check the mailing list or commitfests to confirm which came first and if they were in any way related.

Answer 1

jsonb_delete is not a standard Postgres function. The standard PG way to delete a JSONB field is to use the - operator (to delete a field) or the #- operator (to delete a path). Example:

UPDATE my_table SET data = data #- '{address, state}' WHERE id = 1;

See https://www.postgresql.org/docs/18/functions-json.html for the gory details.

• Zegarek: jsonb_delete by all means is a standard Postgres function, it's what's in the operators you listed.
• keithmo: Interesting. It does indeed seem to exist (select jsonb_delete('{"a":1,"b":2,"{a,b}":3}'::jsonb, 'a', 'b', 'c', 'd', '...'); works as expected) but I cannot find any official documentation on it. postgresql.org/search/?q=jsonb_delete returns zero search results. Is it officially documented anywhere?
• Zegarek: It's not. Quite a lot of functions aren't if all they do is back an operator - that'd just duplicate the operator doc entry.

Answer 2

You're doing -(jsonb,text) by accident, even though your intention is to do -(jsonb,text[]) or #-(jsonb,text[]). Use the actual operator and cast your 2nd argument explicitly - it'll work fine: _{demo at db<>fiddle}

UPDATE my_table SET data = jsonb_delete(data, variadic '{address, state}'::text[]) WHERE id = 1;
--or simply, which is a logical, semantical and functional equivalent:
UPDATE my_table SET data = data - '{address, state}'::text[] WHERE id = 1;
--if you wanted to remove address.state, rather than both "address" and "state"
UPDATE my_table SET data = jsonb_delete_path(data,'{address, state}'::text[]) WHERE id = 1;
UPDATE my_table SET data = data #- '{address, state}'::text[] WHERE id = 1;

Compare what these do, with and without the cast:

select '{"a":{"b":1},"b":2,"{a,b}":3}'::jsonb - '{a,b}';

{"a": {"b": 1}, "b": 2} (third top-level key removed)

select '{"a":{"b":1},"b":2,"{a,b}":3}'::jsonb - '{a,b}'::text[];

{"{a,b}": 3} (first two top-level keys removed)

select '{"a":{"b":1},"b":2,"{a,b}":3}'::jsonb #- '{a,b}'::text[];

{"a": {}, "b": 2, "{a,b}": 3} (nested a.b key removed)

Your query wasn't looking for address and state keys at the top level, or a state key under/inside address, but rather a single key named "{address, state}" at the top level.

That's because the function you used is overloaded and the constant you gave as the 2nd argument can be a text as much as a text[] type value - unless you explicitly assign the type, Postgres will guess the former, which then leads it to select a different variant of the function based on the type it had guessed. Chapter 10. Type Conversion describes the detailed rules of how Postgres is trying to infer or auto-cast types in operator/function calls.

Although jsonb_delete by all means is a standard Postgres function, it's not a great idea to use it directly. If the reason why you started doing that was some sort of a mess in schema search_path and visibility, you can use schema-qualified operator syntax:

select 1 operator(pg_catalog.+) 2; --that's just `1 + 2`

The jsonb_delete functions are normally hidden behind the - operators:

select oid::regoperator,oprcode 
from pg_operator 
where oprcode in(select oid from pg_proc where proname='jsonb_delete')
 or(oprleft='jsonb'::regtype
 and oprresult='jsonb'::regtype
 and oprname~'-');

oid	oprcode
-(jsonb,text)	pg_catalog.jsonb_delete
-(jsonb,text[])	pg_catalog.jsonb_delete
-(jsonb,integer)	pg_catalog.jsonb_delete
#-(jsonb,text[])	jsonb_delete_path
->(jsonb,text)	jsonb_object_field
->(jsonb,integer)	jsonb_array_element

select oid::regprocedure,prosrc
from pg_proc 
where proname='jsonb_delete';

jsonb_delete(jsonb,text)	jsonb_delete
jsonb_delete(jsonb,integer)	jsonb_delete_idx
jsonb_delete(jsonb,text[])	jsonb_delete_array

Even if you wanted to, you can't call jsonb_delete(jsonb,text[]) variant directly, the way you'd expect, at least not that easily:

select jsonb_delete('{"a":1,"b":2,"{a,b}":3}'::jsonb,'{a,b}'::text[]);

ERROR: function jsonb_delete(jsonb, text[]) does not exist
LINE 1: select jsonb_delete('{"a":1,"b":2,"{a,b}":3}'::jsonb,'{a,b}'...
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The reason is that it's actually variadic: the text[] isn't just a 2nd param, it's either that, or a variable-length list of arguments given as a comma-separated list. This means, by default you're expected to call it like this:

select jsonb_delete('{"a":1,"b":2,"{a,b}":3}'::jsonb, 'a', 'b', 'c', 'd', '...');

And if you want to pass them in directly, all at once, in a single array, you need to use the variadic keyword:

select jsonb_delete('{"a":1,"b":2,"{a,b}":3}'::jsonb,variadic '{a,b}'::text[]);

• Zegarek: Since there's just one jsonb_delete_path and #- candidate, the auto-conversion rules will guess text[] correctly in that case, even without the explicit cast.