> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt
> Use this file to discover all available pages before exploring further.

# JSON functions and operators

## JSON functions

### `jsonb_array_elements`

Expands the top-level JSON array into a set of JSON values.

```sql theme={null}
jsonb_array_elements ( jsonb ) → setof jsonb
```

```sql theme={null}
SELECT * FROM jsonb_array_elements('[1,true, [2,false]]'::jsonb);
------RESULT
1
true
[2, false]
```

<Note>
  If you're using `jsonb_array_elements` to unnest arrays for row-level processing and then apply an `array_agg` aggregation,
  it can incur additional overhead due to subplan execution. Use `EXPLAIN` to examine your query plan and check for unexpected JOINs.
  If such joins appear, consider rewriting the query using `array_transform` to perform your mapping logic and avoid subplan execution.
</Note>

For example, given this original SQL:

```sql theme={null}
create table t(key int, payload jsonb);
explain create materialized view m1 as 
  select payload->>'id' as id,
  (select array_agg(value->>'subField' order by ordinality) from jsonb_array_elements(payload->'elements') with ordinality as value) as elements
from t;
```

We can see the query plan contains a join for subplan execution:

```
                                                                                                         QUERY PLAN                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 StreamMaterialize { columns: [id, elements, t._row_id(hidden), t.payload(hidden)], stream_key: [t._row_id, t.payload], pk_columns: [t._row_id, t.payload], pk_conflict: NoCheck }
 └─StreamProject { exprs: [JsonbAccessStr(t.payload, 'id':Varchar) as $expr3, array_agg($expr1 order_by($expr2 ASC)) filter(IsNotNull(1:Int32)), t._row_id, t.payload] }
   └─StreamHashJoin { type: LeftOuter, predicate: t.payload IS NOT DISTINCT FROM t.payload }
     ├─StreamExchange { dist: HashShard(t.payload) }
     │ └─StreamTableScan { table: t, columns: [payload, _row_id] }
     └─StreamProject { exprs: [t.payload, array_agg($expr1 order_by($expr2 ASC)) filter(IsNotNull(1:Int32))] }
       └─StreamHashAgg { group_key: [t.payload], aggs: [array_agg($expr1 order_by($expr2 ASC)) filter(IsNotNull(1:Int32)), count] }
         └─StreamHashJoin { type: LeftOuter, predicate: t.payload IS NOT DISTINCT FROM t.payload }
           ├─StreamProject { exprs: [t.payload] }
           │ └─StreamHashAgg { group_key: [t.payload], aggs: [count] }
           │   └─StreamExchange { dist: HashShard(t.payload) }
           │     └─StreamTableScan { table: t, columns: [payload, _row_id] }
           └─StreamProject { exprs: [t.payload, JsonbAccessStr(JsonbArrayElements(JsonbAccess($0, 'elements':Varchar)), 'subField':Varchar) as $expr1, (projected_row_id + 1:Int64) as $expr2, 1:Int32, projected_row_id] }
             └─StreamProjectSet { select_list: [$0, JsonbArrayElements(JsonbAccess($0, 'elements':Varchar))] }
               └─StreamProject { exprs: [t.payload] }
                 └─StreamHashAgg { group_key: [t.payload], aggs: [count] }
                   └─StreamExchange { dist: HashShard(t.payload) }
                     └─StreamTableScan { table: t, columns: [payload, _row_id] }
(18 rows)
```

If we rewrite it with array\_transform, the plan is greatly simplified:

```sql theme={null}
explain create materialized view m1 as 
  select payload->>'id' as id,
  array_transform(jsonb_to_array(payload->'elements'), |x| x->>'subField') as elements
from t;
```

Query plan after rewrite:

```
                                                                                                          QUERY PLAN                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 StreamMaterialize { columns: [id, elements, t._row_id(hidden)], stream_key: [t._row_id], pk_columns: [t._row_id], pk_conflict: NoCheck }
 └─StreamProject { exprs: [JsonbAccessStr(t.payload, 'id':Varchar) as $expr1, ArrayTransform(JsonbToArray(JsonbAccess(t.payload, 'elements':Varchar)), JsonbAccessStr(t.payload, 'subField':Varchar)) as $expr2, t._row_id] }
   └─StreamTableScan { table: t, columns: [payload, _row_id] }
(3 rows)
```

### `jsonb_array_elements_text`

Expands the top-level JSON array into a set of text (varchar) values.

```sql theme={null}
jsonb_array_elements_text ( jsonb ) → setof varchar
```

```sql theme={null}
SELECT * FROM jsonb_array_elements_text('["foo", "bar"]'::jsonb)
------RESULT
foo
bar
```

### `jsonb_array_length`

Returns the number of elements in the top-level JSON array.

```sql theme={null}
jsonb_array_length ( jsonb ) → integer
```

```sql theme={null}
SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
------RESULT
5
```

### `jsonb_build_array`

Builds a JSON array out of a variadic argument list. Each argument is converted as per `to_jsonb`.

```sql theme={null}
jsonb_build_array ( VARIADIC "any" ) → jsonb
```

```sql theme={null}
SELECT jsonb_build_array(1, 2, 'foo', 4, 5);
------RESULT
 [1, 2, "foo", 4, 5]

SELECT jsonb_build_array(variadic array[1, 2, 4, 5]);
------RESULT
 [1, 2, 4, 5]
```

### `jsonb_build_object`

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. Key arguments are coerced to text; value arguments are converted as per `to_jsonb`.

```sql theme={null}
jsonb_build_object ( VARIADIC "any" ) → jsonb
```

```sql theme={null}
SELECT jsonb_build_object('foo', 1, 2, row(3,'bar'));
------RESULT
{"2": {"f1": 3, "f2": "bar"}, "foo": 1}

SELECT jsonb_build_object(variadic array['foo', '1', '2', 'bar']);
------RESULT
 {"2": "bar", "foo": 1}
```

### `jsonb_each`

Expands the top-level JSON object into a set of key-value pairs.

```sql theme={null}
jsonb_each ( jsonb ) → setof record ( key varchar, value jsonb )
```

```sql theme={null}
SELECT * FROM jsonb_each('{"a":"foo", "b":"bar"}'::jsonb);
------RESULT
a "foo"
b "bar"
```

### `jsonb_each_text`

Expands the top-level JSON object into a set of key-value pairs. The returned values will be of type varchar.

```sql theme={null}
jsonb_each_text ( jsonb ) → setof record ( key varchar, value varchar )
```

```sql theme={null}
SELECT * FROM jsonb_each_text('{"a":"foo", "b":"bar"}'::jsonb);
------RESULT
 key | value
-----+-------
 a   | foo
 b   | bar
```

### `jsonb_extract_path`

Extracts JSON sub-object at the specified path.

This function is equivalent to the [#>](#jsonb--text---jsonb) operator in functionality.

```sql theme={null}
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb
```

* `from_json` is the input JSON value from which to extract the sub-object.
* `path_elems` is the path elements that specify the location of the desired sub-object in the JSON structure. Multiple path elements can be provided as separate arguments.

```sql theme={null}
SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
------RESULT
"foo"

SELECT jsonb_extract_path('{"a": {"b": ["foo","bar"]}}', variadic array['a', 'b', '1']);
------RESULT
 "bar"
```

### `jsonb_extract_path_text`

Extracts JSON sub-object at the specified path as text.

This function is equivalent to the [#>>](#jsonb--text---text) operator in functionality.

```sql theme={null}
jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text
```

```sql theme={null}
SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"string"}}', 'f4', 'f6');
------RESULT
string

SELECT jsonb_extract_path_text('{"a": {"b": ["foo","bar"]}}', variadic array['a', 'b', '1']);
------RESULT
 bar
```

### `jsonb_object_keys`

Returns the set of keys in the top-level JSON object.

```sql theme={null}
jsonb_object_keys ( jsonb ) → setof varchar
```

```sql theme={null}
SELECT * FROM jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}'::jsonb);
------RESULT
f1
f2
```

### `jsonb_strip_nulls`

Removes all object fields that have null values from a given JSONB value, recursively. Empty objects or null values that are not object fields are left untouched.

```sql theme={null}
jsonb_strip_nulls ( jsonb ) → jsonb
```

```sql theme={null}
-- Handling non-null values
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null, "e": 2}}');
------RESULT
{"a": 1, "c": {"e": 2}}

-- Empty object preservation
SELECT jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
------RESULT
{"a": {}, "d": {}}
```

### `jsonb_path_exists`

Checks if a JSON path returns any items from a JSON value.

If the `vars` argument is provided, it must be a JSON object. Its fields act as named values that are substituted into the `path` expression. When the `silent` argument is specified and set to `true`, the function will suppress errors like the [@?](#jsonb--varchar--boolean) and [@@](#jsonb--varchar--boolean-1) operators.

For information on the SQL/JSON Path syntax, refer to the [PostgreSQL documentation](https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH).

```sql theme={null}
jsonb_path_exists ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → boolean
```

```sql theme={null}
SELECT jsonb_path_exists('{"a":1, "b":2, "c":3}', '$.b');
------RESULT
t
```

### `jsonb_path_match`

Evaluates a JSON path predicate on a JSON value and returns the results as a boolean. Only the first item of the result is taken into account.

```sql theme={null}
jsonb_path_match ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → boolean
```

```sql theme={null}
SELECT jsonb_path_match('{"employee":{"name":"John","age":30}}', 'exists($.employee.age ? (@ > 25))');
------RESULT
t
```

### `jsonb_path_query`

Extracts items from a JSON value matching a JSON path and returns as a set.

```sql theme={null}
jsonb_path_query ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → setof jsonb
```

```sql theme={null}
SELECT jsonb_path_query('{
  "employees": [
    {
      "name": "John",
      "age": 30
    },
    {
      "name": "Jane",
      "age": 25
    },
    {
      "name": "David",
      "age": 35
    },
    {
      "name": "Michael",
      "age": 32
    }
  ]
}', '$.employees[*] ? (@.age >= 25 && @.age <= 30)');
------RESULT
       jsonb_path_query
------------------------------
 {"age": 30, "name": "John"}
 {"age": 25, "name": "Jane"}
```

### `jsonb_path_query_array`

Extracts matching JSON path items and returns them wrapped in an array.

```sql theme={null}
jsonb_path_query_array ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → jsonb
```

```sql theme={null}
SELECT jsonb_path_query_array('{
  "employees": [
    {
      "name": "John",
      "age": 30
    },
    {
      "name": "Alice",
      "age": 35
    },
    {
      "name": "Bob",
      "age": 25
    }
  ]
}', '$.employees[*] ? (@.age >= $min && @.age <= $max)', '{"min": 24, "max": 32}');
------RESULT
                  jsonb_path_query_array
-----------------------------------------------------------
 [{"age": 30, "name": "John"}, {"age": 25, "name": "Bob"}]
```

### `jsonb_path_query_first`[](#jsonb%5Fpath%5Fquery%5Ffirst "Direct link to jsonb_path_query_first")

Extracts the first matching item from a JSON value using a JSON path.

```sql theme={null}
jsonb_path_query_first ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → jsonb
```

```sql theme={null}
SELECT jsonb_path_query_first('{
  "employees": [
    {
      "name": "John",
      "age": 30
    },
    {
      "name": "Jane",
      "age": 25
    },
    {
      "name": "David",
      "age": 35
    }
  ]
}', '$.employees[0]');
------RESULT
   jsonb_path_query_first
-----------------------------
 {"age": 30, "name": "John"}

```

### `jsonb_populate_record`

Expands the top-level JSON object to a row having the **struct type** of the base argument.

```sql theme={null}
jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement
```

It scans the JSON object for fields matching the output row's column names, inserting their values into the corresponding output columns. Any fields not matching column names are ignored. Typically the base is `NULL`, meaning that any output columns that do not match any object field will be `NULL`. Otherwise, values in the base are used for unmatched columns.

The conversion of JSON values to SQL types of the output column applies these rules in sequence:

* A JSON null value is converted to a SQL null in all cases.
* If the output column is of type json or jsonb, the JSON value is just reproduced exactly.
* If the output column is a struct (row) type, and the JSON value is a JSON object, the fields of the object are converted to columns of the output row type by recursive application of these rules.
* Likewise, if the output column is an array type and the JSON value is a JSON array, the elements of the JSON array are converted to elements of the output array by recursive application of these rules.
* Otherwise, if the JSON value is a string, the contents of the string are fed to the input conversion function for the column's data type.
* Otherwise, the ordinary text representation of the JSON value is fed to the input conversion function for the column's data type.

```sql theme={null}
SELECT * FROM jsonb_populate_record(
    null::struct<a int, b text[], c struct<d int, e text>>,
    '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}'
);
----RESULT
 a |     b     |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")
```

<Note>
  The `jsonb_populate_record` function in RisingWave differs from the function in PostgreSQL. In PostgreSQL, users are required to define a **composite type** using the `CREATE TYPE` statement before using these functions. However, in RisingWave, you should use the **inline struct type** instead.
</Note>

### `jsonb_populate_recordset`[](#jsonb%5Fpopulate%5Frecordset "Direct link to jsonb_populate_recordset")

Expands the top-level JSON array of objects to a set of rows having the **struct type** of the base argument. Each element of the JSON array is processed as described above for [jsonb\_populate\_record](#jsonb%5Fpopulate%5Frecord).

```sql theme={null}
jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement
```

```sql Example theme={null}
select * from jsonb_populate_recordset(
    null::struct<a int, b int>,
    '[{"a":1,"b":2}, {"a":3,"b":4}]'::jsonb
);
----RESULT
1 2
3 4
```

<Note>
  The `jsonb_populate_recordset` function in RisingWave differs from the function in PostgreSQL. In PostgreSQL, users are required to define a **composite type** using the `CREATE TYPE` statement before using these functions. However, in RisingWave, you should use the **inline struct type** instead.
</Note>

### `jsonb_populate_map`[](#jsonb%5Fpopulate%5Fmap "Direct link to jsonb_populate_map")

Converts `jsonb` data into a `map` type by merging key-value pairs from the `jsonb` into the specified `map`.

```sql theme={null}
jsonb_populate_map(map anymap, from_json jsonb) → map
```

```sql Examples theme={null}
SELECT jsonb_populate_map(
    null::map(varchar, int),
    '{"a": 1, "b": 2}'::jsonb
);
----RESULT
 jsonb_populate_map
--------------------
 {a:1,b:2}

SELECT jsonb_populate_map(
    MAP {'a': 1, 'b': 2},
    '{"b": 3, "c": 4}'::jsonb
);
----RESULT
 jsonb_populate_map
--------------------
 {a:1,b:3,c:4}
```

### `jsonb_set`[](#jsonb%5Fset "Direct link to jsonb_set")

Modifies JSONB data by replacing or inserting new values at a specified path. If the path exists, the function will replace the value with the new one. If the path does not exist and the `create_if_missing` parameter is set to `true` (which is the default), the function will add the new value.

```sql theme={null}
jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb
```

```sql theme={null}
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]') → [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
```

### `jsonb_to_array`

<Note>
  Added in v2.6.0.
</Note>

Converts the a JSONB array to a SQL array of JSONB elements. It is functionally equivalent to [`jsonb_array_elements`](/sql/functions/json#jsonb-array-elements) followed by `array_agg` but simpler to use. `jsonb_to_array` preserves the original element order and accurately distinguishes between `null` and an empty array.

```sql theme={null}
jsonb_to_array ( jsonb ) → jsonb[]
```

```sql theme={null}
SELECT jsonb_to_array('[1,true,[2,false]]'::jsonb);
------RESULT
{1,true,"[2, false]"}
```

### `jsonb_typeof`

Returns the type of the top-level JSON value as a text string.

```sql theme={null}
jsonb_typeof ( jsonb ) → varchar
```

```sql theme={null}
SELECT jsonb_typeof ('-123.4');
------RESULT
number
```

### `jsonb_pretty`

This function takes a `jsonb` value and returns a text representing the formatted, indented JSON value.

```sql theme={null}
jsonb_pretty ( jsonb JSONB ) → TEXT
```

```sql theme={null}
SELECT jsonb_pretty('[{"f1":1,"f2":null}, 2]');
------RESULT
[
    {
        "f1": 1,
        "f2": null
    },
    2
]
```

### `jsonb_object`

This function takes an array of text elements and returns a `jsonb` object where adjacent pairs of values are taken as the key and value of an object property.

```sql theme={null}
jsonb_object ( text_array TEXT[] ) → JSONB
```

```sql theme={null}
jsonb_object('{a, 1, b, def, c, 3.5}' :: text[]) → {"a": "1", "b": "def", "c": "3.5"}
jsonb_object(array['a', null]) → {"a": null}
```

### `to_jsonb`[](#to%5Fjsonb "Direct link to to_jsonb")

Converts any SQL value to JSONB data type. It recursively handles arrays and composites, transforming them into arrays and objects in the resulting JSON representation. If a direct cast from the SQL data type to JSON is available, it is used for the conversion; otherwise, scalar values are produced as JSON scalars, with text representations appropriately escaped to ensure valid JSON string values.

```sql theme={null}
to_jsonb ( any ) → JSONB
```

```sql theme={null}
to_jsonb(array['apple', 'banana', 'cherry']) → ["apple", "banana", "cherry"]
to_jsonb('Products labeled "expired"'::string) → "Products labeled \"expired\""
```

## JSON operators[](#json-operators "Direct link to JSON operators")

### `jsonb -> integer → jsonb`[](#jsonb---integer--jsonb "Direct link to jsonb---integer--jsonb")

Extracts the n'th element of a JSON array (array elements are indexed from zero, but negative integers count from the end).

```sql theme={null}
SELECT'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> 2;
------RESULT
 {"c":"baz"}
```

### `jsonb -> varchar → jsonb`[](#jsonb---varchar--jsonb "Direct link to jsonb---varchar--jsonb")

Extracts JSON object field with the given key.

```sql theme={null}
SELECT '{"a": {"b":"foo"}}'::jsonb -> 'a';
------RESULT
 {"b": "foo"}
```

### `jsonb ->> integer → varchar`[](#jsonb---integer--varchar "Direct link to jsonb---integer--varchar")

Extracts the n'th element of a JSON array, as text.

```sql theme={null}
SELECT '[1,2,3]'::jsonb ->> 2;
------RESULT
3
```

### `jsonb ->> varchar → varchar`

Extracts JSON object field with the given key, as text.

```sql theme={null}
SELECT '{"a":1,"b":2}'::jsonb ->> 'b';
------RESULT
2
```

### `jsonb - text → jsonb`

Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.

```sql theme={null}
'{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"}
'["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]
```

### `jsonb - text[] → jsonb`[](#jsonb---text--jsonb-1 "Direct link to jsonb---text--jsonb-1")

Deletes all matching keys or array elements from a JSON object.

```sql theme={null}
'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}
```

### `jsonb - integer → jsonb`[](#jsonb---integer--jsonb-1 "Direct link to jsonb---integer--jsonb-1")

Deletes the array element with the specified index (negative integers counting from the end). Throws an error if JSON object is not an array.

```sql theme={null}
'["a", "b"]'::jsonb - 1 → ["a"]
```

### `jsonb #- text[] → jsonb`[](#jsonb---text--jsonb-2 "Direct link to jsonb---text--jsonb-2")

Deletes the field or array element at the specified path, where path elements can be either field keys or array indexes.

```sql theme={null}
'["a", {"b":1}]'::jsonb #- '{1,b}' → ["a", {}]
```

### `(jsonb || jsonb) → jsonb`[](#jsonb--jsonb--jsonb "Direct link to jsonb--jsonb--jsonb")

Concatenates jsonb data.

```sql theme={null}
SELECT '["a", "b"]'::jsonb || '["a", "d"]'::jsonb;
SELECT '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb;
SELECT '[1, 2]'::jsonb || '3'::jsonb;
SELECT '{"a": "b"}'::jsonb || '42'::jsonb;
------RESULT
["a", "b", "a", "d"]
{"a": "b", "c": "d"}
[1, 2, 3]
[{"a": "b"}, 42]
```

### `jsonb @> jsonb → boolean`

This operator checks if the left `jsonb` value contains the right `jsonb` value. For a detailed description and examples about containment and existence, see [jsonb Containment and Existence](https://www.postgresql.org/docs/current/datatype-json.html) in PostgreSQL's documentation.

```sql theme={null}
'[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb → t

'{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb → t

'{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb → f

'{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb → t
```

### `jsonb <@ jsonb → boolean`[](#jsonb--jsonb--boolean-1 "Direct link to jsonb--jsonb--boolean-1")

This operator checks if the left `jsonb` value is contained within the right `jsonb` value. For a detailed description and examples about containment and existence, see [jsonb Containment and Existence](https://www.postgresql.org/docs/current/datatype-json.html) in PostgreSQL's documentation.

```sql theme={null}
'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb → t
```

### `jsonb ? text → boolean`[](#jsonb--text--boolean "Direct link to jsonb--text--boolean")

This operator checks if a string exists as a top-level array element or object key within a `jsonb` value.

```sql theme={null}
'["foo", "bar", "baz"]'::jsonb ? 'bar' → t

'{"foo": "bar"}'::jsonb ? 'foo' → t

'{"foo": "bar"}'::jsonb ? 'bar' → f

'{"foo": {"bar": "baz"}}'::jsonb ? 'bar' → f

'"foo"'::jsonb ? 'foo' → t
```

### `jsonb ?| text[] → boolean`[](#jsonb--text--boolean-1 "Direct link to jsonb--text--boolean-1")

This operator checks if any string in an array exists as a top-level array element or object key within a `jsonb` value.

```sql theme={null}
'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

'["a", "b", "c"]'::jsonb ?| array['b', 'd'] → t

'"b"'::jsonb ?| array['b', 'd'] → t
```

### `json ?& text[] → boolean`[](#json--text--boolean "Direct link to json--text--boolean")

This operator checks if all strings in an array exist as top-level array elements or object keys within a `jsonb` value.

```sql theme={null}
'{"a":1, "b":2, "c":3}'::jsonb ?& array['a', 'b'] → t

'["a", "b", "c"]'::jsonb ?& array['a', 'b'] → t

'["a", "b", "c"]'::jsonb ?& array['a', 'd'] → f
```

### `jsonb #> text[] → jsonb`[](#jsonb--text--jsonb "Direct link to jsonb--text--jsonb")

This operator extracts a nested value from a JSONB object using a text array of keys or indices.

```sql theme={null}
'{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,1}'::text[] → "bar"

'{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,null}'::text[] → NULL
```

### `jsonb #>> text[] → text`[](#jsonb--text--text "Direct link to jsonb--text--text")

This operator extracts a nested value as text from a JSONB object using a text array of keys or indices.

```sql theme={null}
'{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,1}'::text[] → bar

'{"a": {"b": ["foo",null]}}'::jsonb #>> '{a,b,1}'::text[] → NULL

'{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,null}'::text[] → NULL
```

### `jsonb @? varchar → boolean`[](#jsonb--varchar--boolean "Direct link to jsonb--varchar--boolean")

Determine whether the specified JSON path returns any item for the given JSON value.

```sql theme={null}
SELECT '{"a":1, "b":2, "c":3}'::jsonb @? '$.a';
------RESULT
t

SELECT '{"a":1, "b":2, "c":3}'::jsonb @? '$.d';
------RESULT
f
```

### `jsonb @@ varchar → boolean`[](#jsonb--varchar--boolean-1 "Direct link to jsonb--varchar--boolean-1")

Returns the result of a JSON path predicate check on the specified JSON value, considering only the first item of the result. If the result is not a Boolean, it returns NULL.

```sql theme={null}
SELECT '{"numbers":[1,2,3,4,5]}'::jsonb @@ '$.numbers[*] == 5';
------RESULT
t
```

## `IS JSON` predicate[](#is-json-predicate "Direct link to is-json-predicate")

This predicate tests whether an expression can be parsed as JSON, optionally of a specified type. It evaluates the JSON structure and returns a boolean result indicating whether the value matches the specified JSON type.

```sql theme={null}
expression IS [ NOT ] JSON [ VALUE | ARRAY | OBJECT | SCALAR ] → bool
```

If SCALAR, ARRAY, or OBJECT is specified, the test is whether or not the JSON is of that particular type.

```sql theme={null}
SELECT js,
  js IS JSON "json?",
  js IS JSON SCALAR "scalar?",
  js IS JSON OBJECT "object?",
  js IS JSON ARRAY "array?"
FROM (VALUES
      ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'),('abc')) foo(js);
```

```sql theme={null}

 js         | json? | scalar? | object? | array?
------------+-------+---------+---------+---------
 123        | t     | t       | f       | f
 "abc"      | t     | t       | f       | f
 {"a": "b"} | t     | f       | t       | f
 [1,2]      | t     | f       | f       | t
 abc        | f     | f       | f       | f
```