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 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'