? 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$;