← Pipelines / pipelines / sql-reference / scalar-functions

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.

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

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

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

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"

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

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

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

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

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"

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 functions provide basic json parsing functions using JsonPath ↗, an evolving standard for querying JSON objects.

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

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'