JSON functions

Query JSON held in text columns. A path is one or more segments—string object keys or integer array indices. The -> / ->> / ? operators are shorthands (json_get / json_as_text / json_contains); casting a -> result, e.g. (data -> 'age')::int, rewrites to the typed getter.

json_as_text

Value at a path, rendered as text. Operator: json ->> key.

json_as_text(json, path...)

json_contains

Whether a value exists at the given path (boolean). Operator: json ? key.

json_contains(json, path...)

json_get

JSON value at a path.

json_get(json, path...)

json_get_array

Value at a path as an array.

json_get_array(json, path...)

json_get_bool

Value at a path as a boolean.

json_get_bool(json, path...)

json_get_float

Value at a path as a float.

json_get_float(json, path...)

json_get_int

Value at a path as an integer.

json_get_int(json, path...)

json_get_json

Nested raw JSON string at a path.

json_get_json(json, path...)

json_get_str

Value at a path as a string.

json_get_str(json, path...)

json_length

Length of the array or object at a path. Alias: json_len.

json_length(json, path...)

json_object_keys

Keys of the object at a path, as an array. Alias: json_keys.

json_object_keys(json, path...)