JSON functions

Cloudflare Pipelines 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

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'