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 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).

SELECT json_contains('{"a": 1, "b": 2, "c": 3}', 'a') FROM source;
true

Also available via the ? operator:

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.).

SELECT json_get('{"a": {"b": 2}}', 'a', 'b') FROM source;
2

Also available via the -> operator:

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:

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.

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.

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.

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.

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.

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.

SELECT json_as_text('{"a": {"b": 42}}', 'a', 'b') FROM source;
"42"

Also available via the ->> operator:

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.

SELECT json_length('{"a": [1, 2, 3]}', 'a') FROM source;
3

Json path functions

JSON functions provide basic json parsing functions using 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.

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.

SELECT extract_json_string('{"a": "a", "b": 2, "c": 3}', '$.a') FROM source;
'a'

Ingest Engine

Get started

Connectors

Streaming SQL

Operate

Reference

Concepts

SQL functions

json_contains

json_get

json_get_str

json_get_int

json_get_float

json_get_bool

json_get_json

json_as_text

json_length

Json path functions

extract_json

extract_json_string

​SQL functions

​json_contains

​json_get

​json_get_str

​json_get_int

​json_get_float

​json_get_bool

​json_get_json

​json_as_text

​json_length

​Json path functions

​extract_json

​extract_json_string

SQL functions

json_contains

json_get

json_get_str

json_get_int

json_get_float

json_get_bool

json_get_json

json_as_text

json_length

Json path functions

extract_json

extract_json_string