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

# JSON

Ingest Engine provides two set of JSON functions, the first based on PostgreSQL's SQL functions and syntax, and the second based on the [JSONPath](https://jsonpath.com/) standard.

**SQL Functions:**

| Function | Description | Example | Result |
| - | - | - | - |
| `json_contains(json, key)` | Check if key exists | `json_contains('{"a":1,"b":2}', 'a')` | `true` |
| `json_get(json, path...)` | Get value by path | `json_get('{"a":{"b":2}}', 'a', 'b')` | `2` |
| `json_get_str(json, path...)` | Get string value | `json_get_str('{"a":"hello"}', 'a')` | `'hello'` |
| `json_get_int(json, path...)` | Get integer value | `json_get_int('{"a":42}', 'a')` | `42` |
| `json_get_float(json, path...)` | Get float value | `json_get_float('{"a":3.14}', 'a')` | `3.14` |
| `json_get_bool(json, path...)` | Get boolean value | `json_get_bool('{"a":true}', 'a')` | `true` |
| `json_get_json(json, path...)` | Get nested JSON | `json_get_json('{"a":{"b":1}}', 'a')` | `'{"b":1}'` |
| `json_as_text(json, path...)` | Get any value as text | `json_as_text('{"a":42}', 'a')` | `'42'` |
| `json_length(json, path...)` | Length of array/object | `json_length('{"a":[1,2,3]}', 'a')` | `3` |

**Operator shortcuts:**

| Operator | Equivalent | Example | Result |
| - | - | - | - |
| `?` | `json_contains` | `'{"a":1}' ? 'a'` | `true` |
| `->` | `json_get` | `'{"a":1}'->'a'` | `1` |
| `->>` | `json_as_text` | `'{"a":1}'->>'a'` | `'1'` |

**JSONPath Functions:**

| Function | Description | Example | Result |
| - | - | - | - |
| `extract_json(json, path)` | Extract matching elements | `extract_json('{"a":1,"b":2}', '$.a')` | `['1']` |
| `extract_json_string(json, path)` | Extract string value | `extract_json_string('{"a":"hello"}', '$.a')` | `'hello'` |

## SQL functions

The SQL functions provide basic JSON parsing functions similar to those found in PostgreSQL.

### json\_contains

Returns `true` if the JSON string contains the specified key(s).

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_contains('{"a": 1, "b": 2, "c": 3}', 'a') FROM source;
true
```

Also available via the `?` operator:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT '{"a": 1, "b": 2, "c": 3}' ? 'a' FROM source;
true
```

### json\_get

Retrieves the value from a JSON string by the specified path (keys). Returns the value as its native type (string, int, etc.).

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_get('{"a": {"b": 2}}', 'a', 'b') FROM source;
2
```

Also available via the `->` operator:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT '{"a": {"b": 2}}'->'a'->'b' FROM source;
2
```

Various permutations of `json_get` functions are available for retrieving values as a specific type, or you can use SQL type annotations:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_get('{"a": {"b": 2}}', 'a', 'b')::int FROM source;
2
```

### json\_get\_str

Retrieves a string value from a JSON string by the specified path. Returns an empty string if the value does not exist or is not a string.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_get_str('{"a": {"b": "hello"}}', 'a', 'b') FROM source;
"hello"
```

### json\_get\_int

Retrieves an integer value from a JSON string by the specified path. Returns `0` if the value does not exist or is not an integer.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_get_int('{"a": {"b": 42}}', 'a', 'b') FROM source;
42
```

### json\_get\_float

Retrieves a float value from a JSON string by the specified path. Returns `0.0` if the value does not exist or is not a float.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_get_float('{"a": {"b": 3.14}}', 'a', 'b') FROM source;
3.14
```

### json\_get\_bool

Retrieves a boolean value from a JSON string by the specified path. Returns `false` if the value does not exist or is not a boolean.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_get_bool('{"a": {"b": true}}', 'a', 'b') FROM source;
true
```

### json\_get\_json

Retrieves a nested JSON string from a JSON string by the specified path. The value is returned as raw JSON.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_get_json('{"a": {"b": {"c": 1}}}', 'a', 'b') FROM source;
'{"c": 1}'
```

### json\_as\_text

Retrieves any value from a JSON string by the specified path and returns it as a string, regardless of the original type.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_as_text('{"a": {"b": 42}}', 'a', 'b') FROM source;
"42"
```

Also available via the `->>` operator:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT '{"a": {"b": 42}}'->>'a'->>'b' FROM source;
"42"
```

### json\_length

Returns the length of a JSON object or array at the specified path. Returns `0` if the path does not exist or is not an object/array.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT json_length('{"a": [1, 2, 3]}', 'a') FROM source;
3
```

## Json path functions

JSON functions provide basic json parsing functions using [JsonPath](https://goessner.net/articles/JsonPath/), an evolving standard for querying JSON objects.

### extract\_json

Returns the JSON elements in the first argument that match the JsonPath in the second argument. The returned value is an array of json strings.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT extract_json('{"a": 1, "b": 2, "c": 3}', '$.a') FROM source;
['1']
```

### extract\_json\_string

Returns an unescaped String for the first item matching the JsonPath, if it is a string.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT extract_json_string('{"a": "a", "b": 2, "c": 3}', '$.a') FROM source;
'a'
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.