> For the complete documentation index, see [llms.txt](https://docs.e6data.com/ingestion-engine/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.e6data.com/ingestion-engine/sql/functions/json.md).

# JSON

e6 Ingestion 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
SELECT json_contains('{"a": 1, "b": 2, "c": 3}', 'a') FROM source;
true
```

Also available via the `?` operator:

```sql
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
SELECT json_get('{"a": {"b": 2}}', 'a', 'b') FROM source;
2
```

Also available via the `->` operator:

```sql
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
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
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
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
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
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
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
SELECT json_as_text('{"a": {"b": 42}}', 'a', 'b') FROM source;
"42"
```

Also available via the `->>` operator:

```sql
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
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
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
SELECT extract_json_string('{"a": "a", "b": 2, "c": 3}', '$.a') FROM source;
'a'
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.e6data.com/ingestion-engine/sql/functions/json.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.