SQL functions
The SQL functions provide basic JSON parsing functions similar to those found in PostgreSQL.json_contains
Returnstrue
if the JSON string contains the specified key(s).
?
operator:
json_get
Retrieves the value from a JSON string by the specified path (keys). Returns the value as its native type (string, int, etc.).->
operator:
json_get
functions are available for retrieving values as
a specific type, or you can use SQL type annotations:
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.json_get_int
Retrieves an integer value from a JSON string by the specified path. Returns0
if the value does not exist or is not an integer.
json_get_float
Retrieves a float value from a JSON string by the specified path. Returns0.0
if the value does not exist or is not a float.
json_get_bool
Retrieves a boolean value from a JSON string by the specified path. Returnsfalse
if the value does not exist or is not a boolean.
json_get_json
Retrieves a nested JSON string from a JSON string by the specified path. The value is returned as raw JSON.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.->>
operator:
json_length
Returns the length of a JSON object or array at the specified path. Returns0
if the path does not exist or is not an object/array.