JSON Functions and Operators

json[i]

JSON array/string indexing.

json[from:to]

JSON array/string slicing.

json[name]

JSON object destructuring.

json = json, json < json, …

Comparison operators.

to_json()

Return JSON value represented by the input string.

to_str()

Render JSON value to a string.

json_get()

Return the JSON value at the end of the specified path or an empty set.

json_array_unpack()

Return elements of JSON array as a set of json.

json_object_unpack()

Return set of key/value tuples that make up the JSON object.

json_typeof()

Return the type of the outermost JSON value as a string.

operator json [ int64 ] -> json

JSON array/string indexing.

The contents of JSON arrays and strings can also be accessed via []:

db> 
SELECT <json>'hello'[1];
{'"e"'}
db> 
SELECT <json>'hello'[-1];
{'"o"'}
db> 
SELECT to_json('[1, "a", null]')[1];
{'"a"'}
db> 
SELECT to_json('[1, "a", null]')[-1];
{'null'}

The element access operator [] will raise an exception if the specified index is not valid for the base JSON value. To access potentially out of bound indexes use the json_get() function.

operator json [ int64 : int64 ] -> json

JSON array/string slicing.

JSON arrays and strings can be sliced in the same way as regular arrays, producing a new JSON array or string:

db> 
SELECT <json>'hello'[0:2];
{'"he"'}
db> 
SELECT <json>'hello'[2:];
{'"llo"'}
db> 
SELECT to_json('[1, 2, 3]')[0:2];
{'[1, 2]'}
db> 
SELECT to_json('[1, 2, 3]')[2:];
{'[3]'}
db> 
SELECT to_json('[1, 2, 3]')[:1];
{'[1]'}
db> 
SELECT to_json('[1, 2, 3]')[:-2];
{'[1]'}
operator json [ str ] -> json

JSON object destructuring.

The fields of JSON objects can also be accessed via []:

db> 
SELECT to_json('{"a": 2, "b": 5}')['b'];
{'5'}
db> 
... 
... 
... 
... 
SELECT j := <json>(schema::Type {
    name,
    timestamp := to_local_date(datetime_current(), 'UTC')
})
FILTER j['name'] = <json>'std::bool';
{'{"name": "std::bool", "timestamp": "2019-04-02"}'}

The field access operator [] will raise an exception if the specified field does not exist for the base JSON value. To access potentially non-existent fields use the json_get() function.

function std::to_json(string: str) -> json

Return JSON value represented by the input string.

db> 
SELECT to_json('[1, "hello", null]')[1];
{'"hello"'}
db> 
SELECT to_json('{"hello": "world"}')['hello'];
{'"world"'}
function std::json_array_unpack(json: json) -> SET OF json

Return elements of JSON array as a set of json.

Calling this function on anything other than a JSON array will cause a runtime error.

This function should be used if the ordering of elements is not important or when set ordering is preserved (such as an immediate input to an aggregate function).

db> 
SELECT json_array_unpack(to_json('[1, "a"]'));
{'1', '"a"'}
function std::json_get(json: json, VARIADIC path: str) -> OPTIONAL json

Return the JSON value at the end of the specified path or an empty set.

This function provides “safe” navigation of a JSON value. If the input path is a valid path for the input JSON object/array, the JSON value at the end of that path is returned. If the path cannot be followed for any reason, the empty set is returned.

db> 
... 
... 
... 
... 
SELECT json_get(to_json('{
    "q": 1,
    "w": [2, "foo"],
    "e": true
}'), 'w', '1');
{'"foo"'}

This is useful when certain structure of JSON data is assumed, but cannot be reliably guaranteed:

db> 
... 
... 
... 
... 
SELECT json_get(to_json('{
    "q": 1,
    "w": [2, "foo"],
    "e": true
}'), 'w', '2');
{}

Also, a default value can be supplied by using the coalescing operator:

db> 
... 
... 
... 
... 
SELECT json_get(to_json('{
    "q": 1,
    "w": [2, "foo"],
    "e": true
}'), 'w', '2') ?? <json>'mydefault';
{'"mydefault"'}
function std::json_object_unpack(json: json) -> SET OF tuple<str, json>

Return set of key/value tuples that make up the JSON object.

Calling this function on anything other than a JSON object will cause a runtime error.

db> 
... 
... 
... 
... 
SELECT json_object_unpack(to_json('{
    "q": 1,
    "w": [2, "foo"],
    "e": true
}'));
{('e', 'true'), ('q', '1'), ('w', '[2, "foo"]')}
function std::json_typeof(json: json) -> str

Return the type of the outermost JSON value as a string.

Possible return values are: 'object', 'array', 'string', 'number', 'boolean', 'null'.

db> 
SELECT json_typeof(<json>2);
{'number'}
db> 
SELECT json_typeof(to_json('null'));
{'null'}
db> 
SELECT json_typeof(to_json('{"a": 2}'));
{'object'}