JSON Functions
JSON Extraction Functions
SereneDB follows PostgreSQL semantics for extracting values from JSON. The -> and ->> operators — and their function equivalents json_extract and json_extract_string — access a member by its name or an array element by its integer index (they do not interpret $. JSONPath or / JSON Pointer syntax). To address a value by a JSON Pointer or JSONPath location instead, use json_value. The operators require the value to be of the JSON logical type.
| Function | Alias | Operator | Description |
|---|---|---|---|
json_exists(json, path) | Returns true if the supplied path exists in the json, and false otherwise. | ||
json_extract(json, key_or_index) | -> | Extracts the named member (string key) or array element (integer index) as JSON. If the argument is a LIST, the result is a LIST of JSON. | |
json_extract_string(json, key_or_index) | ->> | Same as ->, but returns the value as VARCHAR instead of JSON. | |
json_value(json, path) | Extracts the scalar at the given JSON Pointer or JSONPath ($.…) path. Returns NULL if it is not a scalar. |
Note that the arrow operator ->, which is used for JSON extracts, has a low precedence as it is also used in lambda functions. Therefore, you need to surround the -> operator with parentheses when expressing operations such as equality comparisons (=).
For example:
SELECT ((JSON '{"field": 42}')->'field') = 42;?column? ---------- t
The examples below use this dataset:
CREATE TABLE example (j JSON);
INSERT INTO example VALUES ('{ "family": "terrestrial", "species": [ "mercury", "venus", "earth", null ] }');Extract a member by name — json_extract and the -> operator are equivalent and both return JSON:
SELECT json_extract(j, 'family') FROM example;json_extract --------------- "terrestrial"
SELECT j->'family' FROM example;?column? --------------- "terrestrial"
Use ->> to return the value as text (VARCHAR) instead of JSON:
SELECT j->>'family' FROM example;?column? ------------- terrestrial
The operators chain, so a nested array comes back as JSON:
SELECT j->'species' FROM example;?column? --------------------------------------- [ "mercury", "venus", "earth", null ]
Index into an array with an integer (0-based); negative indices count from the end:
SELECT j->'species'->0 FROM example;?column? ----------- "mercury"
SELECT j->'species'->>0 FROM example;?column? ---------- mercury
SELECT j->'species'->-1 FROM example;?column? ---------- null
json_extract_string is the function form of ->>:
SELECT json_extract_string(j, 'family') FROM example;json_extract_string --------------------- terrestrial
To address a value by a JSON Pointer or JSONPath location, use json_value — it accepts both the JSONPath $.… form and the /… pointer form:
SELECT json_value(j, '$.family') FROM example;json_value --------------- "terrestrial"
SELECT json_value(j, '$.species[0]') FROM example;json_value ------------ "mercury"
SELECT json_value(j, '/species/1') FROM example;json_value ------------ "venus"
When the input is a plain VARCHAR string rather than the JSON type, json_extract accepts a JSONPath directly:
SELECT json_extract('{"a": [10, 20]}', '$.a[1]'); json_extract
--------------
20When several values are needed from the same JSON, calling json_extract once per value parses the document repeatedly:
SELECT json_extract(j, 'family') AS family, json_extract(j, 'species') AS speciesFROM example;family | species ---------------+--------------------------------------- "terrestrial" | [ "mercury", "venus", "earth", null ]
Passing a LIST of keys extracts them all in a single pass, which is faster and uses less memory:
WITH extracted AS ( SELECT json_extract(j, ['family', 'species']) AS extracted_list FROM example)SELECT extracted_list[1] AS family, extracted_list[2] AS speciesFROM extracted;family | species ---------------+---------------------------------- "terrestrial" | ["mercury","venus","earth",null]
JSON Scalar Functions
The following scalar JSON functions can be used to gain information about the stored JSON values.
With the exception of json_valid(json), all JSON functions produce an error when invalid JSON is supplied.
We support two kinds of notations to describe locations within JSON: JSON Pointer and JSONPath.
| Function | Description |
|---|---|
json_array_length(json[, path]) | Return the number of elements in the JSON array json, or 0 if it is not a JSON array. If path is specified, return the number of elements in the JSON array at the given path. If path is a LIST, the result will be LIST of array lengths. |
json_contains(json_haystack, json_needle) | Returns true if json_needle is contained in json_haystack. Both parameters are of JSON type, but json_needle can also be a numeric value or a string, however the string must be wrapped in double quotes. |
json_keys(json[, path]) | Returns the keys of json as a LIST of VARCHAR, if json is a JSON object. If path is specified, return the keys of the JSON object at the given path. If path is a LIST, the result will be LIST of LIST of VARCHAR. |
json_structure(json) | Return the structure of json. Defaults to JSON if the structure is inconsistent (e.g., incompatible types in an array). |
json_type(json[, path]) | Return the type of the supplied json, which is one of ARRAY, BIGINT, BOOLEAN, DOUBLE, OBJECT, UBIGINT, VARCHAR and NULL. If path is specified, return the type of the element at the given path. If path is a LIST, the result will be LIST of types. |
json_valid(json) | Return whether json is valid JSON. |
json(json) | Parse and minify json. |
The JSONPointer syntax separates each field with a /.
For example, to extract the first element of the array with key mercury, you can do:
SELECT json_extract('{"mercury": [1, 2, 3]}', '/mercury/0'); json_extract
--------------
1The JSONPath syntax separates fields with a ., and accesses array elements with [i], and always starts with $. Using the same example, we can do the following:
SELECT json_extract('{"mercury": [1, 2, 3]}', '$.mercury[0]'); json_extract
--------------
1Note that SereneDB's JSON data type uses 0-based indexing.
JSONPath is more expressive, and can also access from the back of lists:
SELECT json_extract('{"mercury": [1, 2, 3]}', '$.mercury[#-1]'); json_extract
--------------
3JSONPath also allows escaping syntax tokens, using double quotes:
SELECT json_extract('{"mercury.venus": [1, 2, 3]}', '$."mercury.venus"[1]'); json_extract
--------------
2Examples using a solar-system dataset:
CREATE TABLE example (j JSON);
INSERT INTO example VALUES ('{ "family": "terrestrial", "species": [ "mercury", "venus", "earth", null ] }');SELECT json(j) FROM example; json
---------------------------------------------------------------------
{"family":"terrestrial","species":["mercury","venus","earth",null]}SELECT j.family FROM example;family -------- NULL
SELECT j.species[0] FROM example;species --------- NULL
SELECT json_valid(j) FROM example;json_valid ------------ t
SELECT json_valid('{');json_valid ------------ f
SELECT json_array_length('["mercury", "venus", "earth", null]'); json_array_length
-------------------
4SELECT json_array_length(j, 'species') FROM example; json_array_length
-------------------
4SELECT json_array_length(j, '/species') FROM example; json_array_length
-------------------
4SELECT json_array_length(j, '$.species') FROM example; json_array_length
-------------------
4SELECT json_array_length(j, ['$.species']) FROM example; json_array_length
-------------------
{4}SELECT json_type(j) FROM example;json_type ----------- OBJECT
SELECT json_keys(j) FROM example; json_keys
------------------
{family,species}SELECT json_structure(j) FROM example; json_structure
--------------------------------------------
{"family":"VARCHAR","species":["VARCHAR"]}SELECT json_structure('["mercury", {"family": "terrestrial"}]');json_structure ---------------- ["JSON"]
SELECT json_contains('{"key": "value"}', '"value"');json_contains --------------- t
SELECT json_contains('{"key": 1}', '1');json_contains --------------- t
SELECT json_contains('{"top_key": {"key": "value"}}', '{"key": "value"}');json_contains --------------- t
JSON Aggregate Functions
There are nine JSON aggregate functions.
| Function | Description |
|---|---|
json_group_array(any) | Return a JSON array with all values of any in the aggregation. |
json_group_object(key, value) | Return a JSON object with all key, value pairs in the aggregation. |
json_group_structure(json) | Return the combined json_structure of all json in the aggregation. |
json_agg(any) | Return a JSON array with all values of any in the aggregation, written as PostgreSQL writes it, or NULL when the aggregation has no rows. |
json_agg_strict(any) | Like json_agg, without the NULL values. |
json_object_agg(key, value) | Return a JSON object with all key, value pairs in the aggregation, written as PostgreSQL writes it. A NULL key is an error. |
json_object_agg_strict(key, value) | Like json_object_agg, without the pairs whose value is NULL. |
json_object_agg_unique(key, value) | Like json_object_agg, but a repeated key is an error. |
json_object_agg_unique_strict(key, value) | Like json_object_agg_unique, without the pairs whose value is NULL. |
DISTINCT, FILTER and ORDER BY work on the PostgreSQL ones as on any aggregate, as in json_agg(x ORDER BY x DESC).
Examples:
CREATE TABLE example1 (k VARCHAR, v INTEGER);
INSERT INTO example1 VALUES ('mercury', 42), ('venus', 7);SELECT json_group_array(v) FROM example1;json_group_array ------------------ [42,7]
SELECT json_group_object(k, v) FROM example1; json_group_object
--------------------------
{"mercury":42,"venus":7}CREATE TABLE example2 (j JSON);
INSERT INTO example2 VALUES ('{"family": "terrestrial", "species": ["mercury", "venus"], "coolness": 42.42}'), ('{"family": "gas_giant", "species": ["jupiter", "saturn"], "has_rings": true}');SELECT json_group_structure(j) FROM example2; json_group_structure
--------------------------------------------------------------------------------------
{"family":"VARCHAR","species":["VARCHAR"],"coolness":"DOUBLE","has_rings":"BOOLEAN"}Transforming JSON to Nested Types
In many cases, it is inefficient to extract values from JSON one-by-one.
Instead, we can “extract” all values at once, transforming JSON to the nested types LIST and STRUCT.
| Function | Description |
|---|---|
json_transform(json, structure) | Transform json according to the specified structure. |
from_json(json, structure) | Alias for json_transform. |
json_transform_strict(json, structure) | Same as json_transform, but throws an error when type casting fails. |
from_json_strict(json, structure) | Alias for json_transform_strict. |
The structure argument is JSON of the same form as returned by json_structure.
The structure argument can be modified to transform the JSON into the desired structure and types.
It is possible to extract fewer key/value pairs than are present in the JSON, and it is also possible to extract more: missing keys become NULL.
Examples:
CREATE TABLE example (j JSON);
INSERT INTO example VALUES ('{"family": "terrestrial", "species": ["mercury", "venus"], "coolness": 42.42}'), ('{"family": "gas_giant", "species": ["jupiter", "saturn"], "has_rings": true}');SELECT json_transform(j, '{"family": "VARCHAR", "coolness": "DOUBLE"}') FROM example;json_transform --------------------- (terrestrial,42.42) (gas_giant,)
SELECT json_transform(j, '{"family": "TINYINT", "coolness": "DECIMAL(4, 2)"}') FROM example;json_transform ---------------- (,42.42) (,)
SELECT json_transform_strict(j, '{"family": "TINYINT", "coolness": "DOUBLE"}') FROM example;db error: ERROR: Failed to cast value to numerical: "gas_giant"
JSON Table Functions
SereneDB implements two JSON table functions that take a JSON value and produce a table from it.
| Function | Description |
|---|---|
json_each(json[ ,path] | Traverse json and return one row for each element in the top-level array or object. |
json_tree(json[ ,path] | Traverse json in depth-first fashion and return one row for each element in the structure. |
If the element is not an array or object, the element itself is returned.
If the optional path argument is supplied, traversal starts from the element at the given path instead of the root element.
The resulting table has the following columns:
| Field | Type | Description |
|---|---|---|
key | VARCHAR | Key of element relative to its parent |
value | JSON | Value of element |
type | VARCHAR | json_type (function) of this element |
atom | JSON | json_value (function) of this element |
id | UBIGINT | Element identifier, numbered by parse order |
parent | UBIGINT | id of parent element |
fullkey | VARCHAR | JSON path to element |
path | VARCHAR | JSON path to parent element |
json | JSON (Virtual) | The json parameter |
root | TEXT (Virtual) | The path parameter |
rowid | BIGINT (Virtual) | The row identifier |
These functions are analogous to SQLite's functions with the same name.
Note that, because the json_each and json_tree functions refer to previous subqueries in the same FROM clause, they are lateral joins.
Examples:
CREATE TABLE example (j JSON);
INSERT INTO example VALUES ('{"family": "terrestrial", "species": ["mercury", "venus"], "coolness": 42.42}'), ('{"family": "gas_giant", "species": ["jupiter", "saturn"], "has_rings": true}');SELECT je.*, je.rowidFROM example AS e, json_each(e.j) AS jeORDER BY e.j::TEXT, je.id;key | value | type | atom | id | parent | fullkey | path | rowid -----------+----------------------+---------+---------------+----+--------+-------------+------+------- family | "gas_giant" | VARCHAR | "gas_giant" | 2 | NULL | $.family | $ | 0 species | ["jupiter","saturn"] | ARRAY | NULL | 4 | NULL | $.species | $ | 1 has_rings | true | BOOLEAN | true | 8 | NULL | $.has_rings | $ | 2 family | "terrestrial" | VARCHAR | "terrestrial" | 2 | NULL | $.family | $ | 0 species | ["mercury","venus"] | ARRAY | NULL | 4 | NULL | $.species | $ | 1 coolness | 42.42 | DOUBLE | 42.42 | 8 | NULL | $.coolness | $ | 2
SELECT je.*, je.rowidFROM example AS e, json_each(e.j, '$.species') AS jeORDER BY e.j::TEXT, je.id;key | value | type | atom | id | parent | fullkey | path | rowid -----+-----------+---------+-----------+----+--------+--------------+-----------+------- 0 | "jupiter" | VARCHAR | "jupiter" | 5 | NULL | $.species[0] | $.species | 0 1 | "saturn" | VARCHAR | "saturn" | 6 | NULL | $.species[1] | $.species | 1 0 | "mercury" | VARCHAR | "mercury" | 5 | NULL | $.species[0] | $.species | 0 1 | "venus" | VARCHAR | "venus" | 6 | NULL | $.species[1] | $.species | 1
SELECT je.key, je.value, je.type, je.id, je.parent, je.fullkey, je.rowidFROM example AS e, json_tree(e.j) AS jeORDER BY e.j::TEXT, je.id; key | value | type | id | parent | fullkey | rowid
-----------+-------------------------------------------------------------------------+---------+----+--------+--------------+-------
NULL | {"family":"gas_giant","species":["jupiter","saturn"],"has_rings":true} | OBJECT | 0 | NULL | $ | 0
family | "gas_giant" | VARCHAR | 2 | 0 | $.family | 1
species | ["jupiter","saturn"] | ARRAY | 4 | 0 | $.species | 2
0 | "jupiter" | VARCHAR | 5 | 4 | $.species[0] | 3
1 | "saturn" | VARCHAR | 6 | 4 | $.species[1] | 4
has_rings | true | BOOLEAN | 8 | 0 | $.has_rings | 5
NULL | {"family":"terrestrial","species":["mercury","venus"],"coolness":42.42} | OBJECT | 0 | NULL | $ | 0
family | "terrestrial" | VARCHAR | 2 | 0 | $.family | 1
species | ["mercury","venus"] | ARRAY | 4 | 0 | $.species | 2
0 | "mercury" | VARCHAR | 5 | 4 | $.species[0] | 3
1 | "venus" | VARCHAR | 6 | 4 | $.species[1] | 4
coolness | 42.42 | DOUBLE | 8 | 0 | $.coolness | 5JSON Set-Returning Functions
These functions return one row per element of a JSON array or per member of a JSON object.
| Function | Columns | Description |
|---|---|---|
json_array_elements(json) | value JSON | One row per element of the top-level array. |
json_array_elements_text(json) | value TEXT | Like json_array_elements, as text: a string without its quotes and escapes, a JSON null as NULL. |
json_each_text(json) | key TEXT, value TEXT | One row per member of the top-level object, the value as text like in json_array_elements_text. |
json_object_keys(json) | json_object_keys TEXT | One row per key of the top-level object. |
Elements and members come in the order of the input, and a repeated key gives a row per occurrence. json_array_elements returns each element with the spacing it has in the input. A NULL input returns no rows. An array where an object is expected, an object where an array is expected, or a scalar is an error. In the select list, these functions return a row per element as well; json_each_text then returns each row as a record.
Examples:
SELECT e.j->>'family' AS family, s.value AS speciesFROM example AS e, json_array_elements_text(e.j->'species') AS sORDER BY family, species;family | species -------------+--------- gas_giant | jupiter gas_giant | saturn terrestrial | mercury terrestrial | venus
SELECT k.key, k.valueFROM json_each_text('{"name": "mars", "moons": 2, "rings": null}') AS kORDER BY k.key;
SELECT json_object_keys('{"name": "mars", "moons": 2}') AS keyORDER BY key;key | value -------+------- moons | 2 name | mars rings | NULL key ------- moons name
JSON Record Functions
These functions turn JSON objects into rows.
| Function | Description |
|---|---|
json_populate_record(base, json[, use_json_as_text]) | A value of the composite type of base built from the top-level object. Members missing from json take their values from base. |
json_populate_recordset(base, json[, use_json_as_text]) | One row of the composite type of base per object of the top-level array, missing members taken from base. |
A column takes the member of the same name, matched case-sensitively; a missing member or a JSON null gives NULL. A JSON string is converted to the column type from its text without quotes and escapes, any other value from its JSON text, so a TEXT column takes a nested array or object as JSON text and a JSON column takes the value as JSON. An array column takes a JSON array, a composite column takes a JSON object, and either also takes a string in the type's text form. A value that does not convert to its column type is an error. use_json_as_text is accepted and has no effect.
For a NULL input, json_populate_record returns base and json_populate_recordset returns no rows. Both also work in the select list, where they return records.
Example:
CREATE TYPE planet AS (name TEXT, moons INTEGER);
SELECT *FROM json_populate_record(NULL::planet, '{"name": "mars", "moons": 2}');name | moons ------+------- mars | 2