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

> Reference material for JSON operators

# JSON Operators

# Operators for Native JSON Values

The following operators are supported on `JSON` values.

## Dot Operator `.`

Extract the nested `JSON` value with a given field name from a `JSON` object. If a nested value with the requested field name does not exist `NULL` is returned.

### Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
<json>.<field_name>
```

### Parameters

| Parameter      | Description                                                          | Supported Input Types |
| :------------- | :------------------------------------------------------------------- | :-------------------- |
| `<json>`       | The JSON value from which the field should be extracted.             | `JSON`                |
| `<field_name>` | A SQL identifier specifying the field name that should be extracted. | SQL identifier        |

Note that `<field_name>` can be double-quoted if necessary, e.g. in order to write `<json>."key with spaces"` or `<json>."1"`. The dot operator can only be used to extract nested values from JSON objects, i.e. it returns a non-NULL result only if `<json>` is a JSON object that contains `<field_name>` as a key. If `<json>` is a JSON array the operator returns NULL unconditionally, even if `<field_name>` happens to contains a valid zero-based array index.

### Return Type

`JSON`

* If the `<json>` input value is `NULL` the operator will return `NULL`.
* If a field with the requested name does not exist the operator will return `NULL`.

## Subscript Operator `[]`

Extract a field of a `JSON` value as another `JSON` value. Matches against both JSON object entries and JSON array entries in the input. If a field with the requested name or index does not exist `NULL` is returned.

### Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
<json>[<field_name_or_array_index>]
```

### Parameters

| Parameter                     | Description                                                                                         | Supported Input Types          |
| :---------------------------- | :-------------------------------------------------------------------------------------------------- | :----------------------------- |
| `<json>`                      | The JSON value from which the field should be extracted.                                            | `JSON`                         |
| `<field_name_or_array_index>` | A TEXT, INTEGER, or BIGINT value specifying the field name or array index that should be extracted. | `TEXT`, `INTEGER`, or `BIGINT` |

This operator does not distinguish between JSON objects and JSON arrays in any way, i.e. it returns a non-NULL result both if `<json>` is a JSON object that contains `<field_name_or_array_index>` as a key and if `<json>` is a JSON array and `<field_name_or_array_index>` contains a valid zero-based array index. The type of the `<field_name_or_array_index>` parameter has no effect on the query result. More specifically, when `<json>` contains a JSON object the parameter value is always converted to `TEXT` internally. Conversely, when `<json>` contains a JSON array the parameter value is always converted to `BIGINT` internally. If this conversion fails the operator will return `NULL`. Unlike the dot operator, the subscript operator accepts non-literal `<field_name_or_array_index>` parameters.

### Return Type

`JSON`

* If the `<json>` input value is `NULL` the operator will return `NULL`.
* If a field with the requested name or index does not exist the operator will return `NULL`.

## Examples

The following table is used in all examples:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE t (col JSON);
INSERT INTO t VALUES ('
{
  "a": [
    42,
    {
      "b": {
        "message": "hello world!",
        "42": "the answer"
      }
    },
    42
  ]
}
');
```

**Example**

The following queries attempt to extract various non-existent values, leading to a `NULL` return value in all cases:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT col.non_existent    FROM t;  -- JSON object has no entry with key "non_existent"
SELECT col['non_existent'] FROM t;  -- JSON object has no entry with key "non_existent"

SELECT col.a."1"           FROM t;  -- Dot operator cannot be used to access JSON array elements
SELECT col.a[3]            FROM t;  -- Index 3 is out of bounds for JSON array of length 3
SELECT col.a['3']          FROM t;  -- Index 3 is out of bounds for JSON array of length 3

SELECT col.a['foo']        FROM t;  -- col.a is a JSON array but "foo" cannot be converted to BIGINT
```

Returns in all cases:

| ?column? (JSON) |
| :-------------- |
| NULL            |

**Example**

The following query extracts an existing field from a JSON object.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT col.a    FROM t;
SELECT col."a"  FROM t;
SELECT col['a'] FROM t;
```

Returns in all cases:

| ?column? (JSON)                                              |
| :----------------------------------------------------------- |
| `[42,{"b":{"message":"hello world!","42":"the answer"}},42]` |

**Example**

The following queries extract an existing element from a JSON array. Note that the parameter type passed to the subscript operator has no effect on the query result.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT col.a[0]   FROM t;
SELECT col.a['0'] FROM t;
```

Returns in all cases:

| ?column? (JSON) |
| :-------------- |
| `42`            |

**Example**

The following query extracts an existing field from a JSON object. Note that the parameter type passed to the subscript operator has no effect on the query result.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT col.a[1].b."42"  FROM t;
SELECT col.a[1].b[42]   FROM t;
SELECT col.a[1].b['42'] FROM t;
```

Returns in all cases:

| ?column? (JSON) |
| :-------------- |
| `"the answer"`  |

**Example**

The following query passes a non-literal `<field_name_or_array_index>` parameter to the subscript operator.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT index, col.a[index] FROM t, generate_series(-1,3) g(index);
```

Returns:

| index INTEGER | ?column? (JSON)                                        |
| :------------ | :----------------------------------------------------- |
| `-1`          | `NULL`                                                 |
| `0`           | `'42'`                                                 |
| `1`           | `'{"b":{"42":"the answer","message":"hello world!"}}'` |
| `2`           | `'42'`                                                 |
| `3`           | `NULL`                                                 |

# Operators for TEXT Values Storing JSON Strings

The following operators are supported on `TEXT` values storing JSON strings. Note that each invocation of these operators has to re-parse the input JSON string from scratch, making them substantially less efficient than the native JSON operators described above.

## Operator `->>`

Get value of a JSON field as text.

It never looks into nested JSON documents. Special characters like `/` in field names are escaped and not interpreted as JSON path separators.

### Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
<json>->>'<field_name>'
```

### Parameters

| Parameter      | Description                                                     | Supported input types |
| :------------- | :-------------------------------------------------------------- | :-------------------- |
| `<json>`       | The JSON document.                                              | `TEXT`                |
| `<field_name>` | Name of the JSON field for which the value should be extracted. | `TEXT`                |

### Return Type

`TEXT`

* If any input values are `NULL`, the function will return `NULL`.
* If the field name is not found in the JSON document, the function returns `NULL`.

### Examples

The following `CREATE TABLE` and `INSERT` statements set up the examples.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
create table json_operators (s text);

insert into json_operators
values ('
{
    "key": 123,
    "key~with~tilde": 2,
    "key/with/slash": 3,
    "value": {
      "dyid": 987,
      "keywords" : ["insanely","fast","analytics"]
    }
}
');
```

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT s->>'key' FROM json_operators;
```

**Returns**

| ?column? (TEXT) |
| :-------------- |
| '123'           |

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT s->>'key/with/slash' FROM json_operators;
```

**Returns**

| ?column? (TEXT) |
| :-------------- |
| '3'             |

There is no need to escape `~` and `/` in the field name when using the `->>` operator.

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT s->>'value' FROM json_operators;
```

**Returns**

| ?column? (TEXT)                                                                        |
| :------------------------------------------------------------------------------------- |
| `'{\n      "dyid": 987,\n      "keywords" : ["insanely","fast","analytics"]\n    }\n'` |

This example returns the nested JSON document associated to the JSON field `value` as a `TEXT` value.

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT s->>'value'->>'dyid' FROM json_operators;
```

**Returns**

| ?column? (TEXT) |
| :-------------- |
| '987'           |

The first `->>` operator retrieves the nested JSON document in text form. Applying the second `->>` operator on the nested JSON document returns the value associated with the JSON field `dyid`. The operator returns this value as a string.

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT s->>'value/dyid' FROM json_operators;
```

**Returns**

| ?column? (TEXT) |
| :-------------- |
| NULL            |

The JSON document has no field called `value/dyid`. The `->>` operator never looks into nested JSON documents.