? and ?| and ?& (key or value existence operators)
Purpose: (1) If the left-hand JSON value is an object, test if the right-hand SQL text value(s) exist as key name(s) in the object. (2) If the left-hand JSON value is an array, test if the right-hand SQL text value(s) exist as JSON string value(s) in the array.
Notes: Each of these operators requires that the input is presented as jsonb value. There are no json overloads. The first variant allows a single text value to be provided. The second and third variants allow a list of text values to be provided. The second is the or (any) flavor and the third is the and (all) flavor.
The ? operator
Purpose: If the left-hand JSON value is an object , test if it has a key-value pair with a key whose name is given by the right-hand scalar text value. If the left-hand JSON value is an array test if it has a string value given by the right-hand scalar text value.
Signature:
input values: jsonb ? text
return value: boolean
Input is an object:
Here, the existence expression evaluates to TRUE so the ASSERT succeeds:
do $body$
declare
j constant jsonb := '{"a": "x", "b": "y"}';
key constant text := 'a';
begin
assert
j ? key,
'unexpected';
end;
$body$;
Here, the existence expression for this counter-example evaluates to FALSE because the left-hand JSON value has "x" as a value and not as a key.
do $body$
declare
j constant jsonb := '{"a": "x", "b": "y"}';
key constant text := 'x';
begin
assert
not (j ? key),
'unexpected';
end;
$body$;
Here, the existence expression for this counter-example evaluates to FALSE because the left-hand JSON value has the object not at top-level but as the second subvalue in a top-level array:
do $body$
declare
j constant jsonb := '[1, {"a": "x", "b": "y"}]';
key constant text := 'a';
begin
assert
not (j ? key),
'unexpected';
end;
$body$;
Input is an array:
do $body$
declare
j_str_arr constant jsonb := '["cat", "dog", "from"]';
t_string constant text := 'dog';
begin
assert
j_str_arr ? t_string,
'unexpected';
end;
$body$;
Further clarification of semantics:
The only possible (and useful) match for the right-hand scalar SQL text value is a key name in an object or a string value in an array. Here are the counter-examples for the other primitive and compound JSON values:
do $body$
declare
-- Positive test
j_string constant jsonb := '"v1"';
t_string constant text := 'v1';
-- Negative tests
j_number constant jsonb := '42';
j_boolean constant jsonb := 'true';
j_null constant jsonb := 'null';
j_object constant jsonb := '{"k1": "v1"}';
j_array constant jsonb := '[1, 2, 3]';
t_number constant text := '42';
t_boolean constant text := 'true';
t_null constant text := 'null';
t_object constant text := '{"k1": "v1"}';
t_array constant text := '[1, 2, 3]';
begin
assert
(j_string ? t_string ) and
not(j_number ? t_number ) and
not(j_boolean ? t_boolean) and
not(j_boolean ? t_boolean) and
not(j_null ? t_null ) and
not(j_object ? t_object ) and
not(j_array ? t_array ),
'unexpected';
end;
$body$;
Notice that this test:
(j_string ? t_string )
where the left hand value is a primitive JSON string, has no practical utility. It is included just to demonstrate the level of granularity at which the test is applied for the "exists as value in array" use of the ? operator.
The ?| operator
Purpose: If the left-hand JSON value is an object, test if it has at least one key-value pair where the key name is present in the right-hand list of scalar text values. If the left-hand JSON value is an array, test if it has at least one string value that is present in the right-hand list of scalar text values.
Signature:
input values: jsonb ?| text[]
return value: boolean
Input is an object:
Here, the existence expression evaluates to TRUE.
do $body$
declare
j constant jsonb := '{"a": "x", "b": "y", "c": "z"}';
key_list constant text[] := array['a', 'p'];
begin
assert
j ?| key_list,
'unexpected';
end;
$body$;
Here, the existence expression for this counter-example evaluates to FALSE because none of the text values in the right-hand array exists as the key of a key-value pair.
do $body$
declare
j constant jsonb := '{"a": "x", "b": "y", "c": "z"}';
key_list constant text[] := array['x', 'p'];
begin
assert
not (j ?| key_list),
'unexpected';
end;
$body$;
('x' in the right-hand key list exists only as a primitive string value for the key "a".)
Input is an array:
Here, the existence expression evaluates to TRUE.
do $body$
declare
j constant jsonb := '["a", "b", "c"]';
key_list constant text[] := array['a', 'p'];
begin
assert
(j ?| key_list),
'unexpected';
end;
$body$;
The ?& operator
Purpose: If the left-hand JSON value is an object, test if every value in the right-hand list of scalar text values is present as the name of the key of a key-value pair. If the left-hand JSON value is an array, test if every value in the right-hand list of scalar text values is present as a string value in the array.
Signature:
input values: jsonb ?& text[]
return value: boolean
Input is an object:
Here, the existence expression evaluates to true:
do $body$
declare
j constant jsonb := '{"a": "w", "b": "x", "c": "y", "d": "z"}';
key_list constant text[] := array['a', 'b', 'c'];
begin
assert
j ?& key_list,
'unexpected';
end;
$body$;
Here, the existence expression for this counter-example evaluates to FALSE because 'z' in the right-hand key list exists as the value of a key-value pair, but not as the key of such a pair.
do $body$
declare
j constant jsonb := '{"a": "x", "b": "y", "c": "z"}';
key_list constant text[] := array['a', 'b', 'z'];
begin
assert
not(j ?& key_list),
'unexpected';
end;
$body$;
Input is an array:
Here, existence expression evaluates to true:
do $body$
declare
j constant jsonb := '["a", "b", "c", "d"]';
key_list constant text[] := array['a', 'b', 'c'];
begin
assert
j ?& key_list,
'unexpected';
end;
$body$;