Overload functions

jsonb_exists_any (?|) and jsonb_exists_all (?&) have a silent argument. You can use the argument to suppress errors that are caused by invalid JSON keys in the keys argument.

jsonb_exists_any and jsonb_exists_all

By default, jsonb_exists_any() and jsonb_exists_all() return an error if the second argument contains an invalid key (a non-string value). For example, in the queries, 1 is an invalid key.

Example:

SELECT jsonb_exists_any('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age", 1]');
ERROR:  Second argument contains unexpected type: number
Hint:  Second argument must be a single dimensional JSONB array of strings

SELECT jsonb_exists_all('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age" , 1]');
ERROR:  Second argument contains unexpected type: number
Hint:  Second argument must be a single dimensional JSONB array of strings

You might suppress the error by providing silent as the third argument to jsonb_exists_any() and jsonb_exists_all().

Example:

SELECT jsonb_exists_any('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age", 1]', true);
 JSONB_EXISTS_ANY 
------------------
 t
(1 row)

SELECT jsonb_exists_all('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age" , 1]', true);
 JSONB_EXISTS_ALL 
------------------
 f
(1 row)

?| and ?&

By default, the ?| (jsonb_exists_any()) and ?& (jsonb_exists_all()) operators are in strict mode. The query returns an error if the right operand contains invalid keys.

For example, the right operand of the operations contains an invalid key, 1. It is considered an error.

Example:

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ?| '["age", 1]';
ERROR:  Second argument contains unexpected type: number
Hint:  Second argument must be a single dimensional JSONB array of strings

SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ?& '["age" , 1]';
ERROR:  Second argument contains unexpected type: number
Hint:  Second argument must be a single dimensional JSONB array of strings

You might suppress the error by providing silent as the third argument to the underlying function of ?|, jsonb_exists_any(), and ?&, jsonb_exists_all().

For example, in the queries, the invalid key (1) is ignored and considered to be nonexistent.

Example:

SELECT jsonb_exists_any('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age", 1]', true);
 JSONB_EXISTS_ANY 
------------------
 t
(1 row)

SELECT jsonb_exists_all('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age", 1]', true);
 JSONB_EXISTS_ALL 
------------------
 f
(1 row)