DocumentationArchitectureBlogAboutSpeak to an engineer
Hotdata
Reference›JSON functions

JSON functions

Query JSON held in text columns. A path is one or more segments—string object keys or integer array indices—and each segment is a separate argument: the value at user.name is json_get_str(data, 'user', 'name'). The -> and ->> operators are shorthands for json_get and json_as_text; chain them to descend (data -> 'user' ->> 'name'). Casting a -> result, e.g. (data -> 'age')::int, rewrites to the typed getter.

Two things the signatures do not show:

  • JSONPath is not supported. json_get_str(data, '$.user.name') returns NULL rather than an error, so a JSONPath string reads as a missing key.
  • . is not a path separator. data ->> 'a.b' looks up a key named literally a.b, which is why it finds the value in {"a.b": 1} and returns NULL on {"a": {"b": 1}}.
-- The two spellings that reach a nested value, and the two that look like they should.
> SELECT data -> 'user' ->> 'name'          AS chained,
         json_as_text(data, 'user', 'name') AS variadic,
         json_get_str(data, '$.user.name')  AS jsonpath,
         data ->> 'user.name'               AS dotted
FROM VALUES ('{"user": {"name": "ada", "age": 36}}') t(data);
+---------+----------+----------+--------+
| chained | variadic | jsonpath | dotted |
+---------+----------+----------+--------+
| ada     | ada      |          |        |
+---------+----------+----------+--------+
1 row(s) fetched.

Every example below queries the same document:

{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}

json_as_text

Value at a path, rendered as text: strings unquoted, everything else as its JSON text. Operator: json ->> key.

json_as_text(json, path...)

> SELECT data ->> 'tags' AS tags, data -> 'user' ->> 'name' AS name, json_as_text(data, 'user', 'age') AS age
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+-----------------+------+-----+
| tags            | name | age |
+-----------------+------+-----+
| ["sql", "json"] | ada  | 36  |
+-----------------+------+-----+
1 row(s) fetched.

json_contains

Whether a value exists at the given path (boolean). There is no operator form—? is reserved as a parameter placeholder and does not parse.

json_contains(json, path...)

> SELECT json_contains(data, 'user') AS has_user, json_contains(data, 'user', 'name') AS has_name, json_contains(data, 'user', 'email') AS has_email
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+----------+----------+-----------+
| has_user | has_name | has_email |
+----------+----------+-----------+
| true     | true     | false     |
+----------+----------+-----------+
1 row(s) fetched.

json_get

JSON value at a path. Objects come back re-serialized with keys sorted; use json_get_json to keep the source text. Operator: json -> key.

json_get(json, path...)

> SELECT json_get(data, 'user', 'name') AS name, json_get(data, 'tags', 0) AS first_tag
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+------+-----------+
| name | first_tag |
+------+-----------+
| ada  | sql       |
+------+-----------+
1 row(s) fetched.

json_get_array

Value at a path as an array. Each element is its own raw JSON text, so strings keep their quotes.

json_get_array(json, path...)

> SELECT json_get_array(data, 'tags') AS tags
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+-------------------------+
| tags                    |
+-------------------------+
| ["\"sql\"", "\"json\""] |
+-------------------------+
1 row(s) fetched.

json_get_bool

Value at a path as a boolean.

json_get_bool(json, path...)

> SELECT json_get_bool(data, 'user', 'admin') AS admin
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+-------+
| admin |
+-------+
| true  |
+-------+
1 row(s) fetched.

json_get_float

Value at a path as a float.

json_get_float(json, path...)

> SELECT json_get_float(data, 'user', 'score') AS score
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+-------+
| score |
+-------+
| 9.5   |
+-------+
1 row(s) fetched.

json_get_int

Value at a path as an integer. Casting a -> chain reaches the same value.

json_get_int(json, path...)

> SELECT json_get_int(data, 'user', 'age') AS age, (data -> 'user' -> 'age')::int AS age_cast
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+-----+----------+
| age | age_cast |
+-----+----------+
| 36  | 36       |
+-----+----------+
1 row(s) fetched.

json_get_json

Nested raw JSON string at a path, exactly as it appears in the source document.

json_get_json(json, path...)

> SELECT json_get_json(data, 'user') AS user_json
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+---------------------------------------------------------+
| user_json                                               |
+---------------------------------------------------------+
| {"name": "ada", "age": 36, "admin": true, "score": 9.5} |
+---------------------------------------------------------+
1 row(s) fetched.

json_get_str

Value at a path as a string. NULL when the value is not a JSON string, so a number stays out of reach here—use json_get_int or json_as_text.

json_get_str(json, path...)

> SELECT json_get_str(data, 'user', 'name') AS name, json_get_str(data, 'user', 'age') AS age
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+------+-----+
| name | age |
+------+-----+
| ada  |     |
+------+-----+
1 row(s) fetched.

json_length

Length of the array or object at a path—elements for an array, keys for an object. Alias: json_len.

json_length(json, path...)

> SELECT json_length(data, 'tags') AS tag_count, json_length(data, 'user') AS user_keys
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+-----------+-----------+
| tag_count | user_keys |
+-----------+-----------+
| 2         | 4         |
+-----------+-----------+
1 row(s) fetched.

json_object_keys

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

json_object_keys(json, path...)

> SELECT json_object_keys(data, 'user') AS user_keys
FROM VALUES ('{"user": {"name": "ada", "age": 36, "admin": true, "score": 9.5}, "tags": ["sql", "json"]}') t(data);
+-----------------------------------+
| user_keys                         |
+-----------------------------------+
| ["name", "age", "admin", "score"] |
+-----------------------------------+
1 row(s) fetched.