json[i]

JSON array/string indexing.

json[from:to]

JSON array/string slicing.

json[name]

JSON object property access.

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[i]
json [ int64 ] -> json

JSON array/string indexing.

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

Copy
db> 
SELECT <json>'hello'[1];
{'"e"'}
Copy
db> 
SELECT <json>'hello'[-1];
{'"o"'}
Copy
db> 
SELECT to_json('[1, "a", null]')[1];
{'"a"'}
Copy
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[from:to]
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:

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

JSON object property access.

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

Copy
db> 
SELECT to_json('{"a": 2, "b": 5}')['b'];
{'5'}
Copy
db> 
... 
... 
... 
... 
SELECT j := <json>(schema::Type {
    name,
    timestamp := cal::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
to_json()
std::to_json(string: str) -> json

Return JSON value represented by the input string.

Copy
db> 
SELECT to_json('[1, "hello", null]')[1];
{'"hello"'}
Copy
db> 
SELECT to_json('{"hello": "world"}')['hello'];
{'"world"'}
function
json_array_unpack()
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).

Copy
db> 
SELECT json_array_unpack(to_json('[1, "a"]'));
{'1', '"a"'}
function
json_get()
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.

Copy
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:

Copy
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:

Copy
db> 
... 
... 
... 
... 
SELECT json_get(to_json('{
    "q": 1,
    "w": [2, "foo"],
    "e": true
}'), 'w', '2') ?? <json>'mydefault';
{'"mydefault"'}
function
json_object_unpack()
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.

Copy
db> 
... 
... 
... 
... 
SELECT json_object_unpack(to_json('{
    "q": 1,
    "w": [2, "foo"],
    "e": true
}'));
{('e', 'true'), ('q', '1'), ('w', '[2, "foo"]')}
function
json_typeof()
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'.

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