JSON Functions and Operators

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.

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]'}

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 := 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.

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"'}

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"'}

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"'}

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"]')}

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'}