JSON functions
Scalar functions for manipulating JSON
Arroyo 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'