json

JSON scalar type

json[i]

JSON array/string indexing.

json[from:to]

JSON array/string slicing.

json ++ json

JSON concatenation.

json[name]

JSON object property access.

= != ?= ?!= < > <= >=

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.

JSON in EdgeDB is one of the scalar types. This scalar doesn’t have its own literal and instead can be obtained by casting a value into json or by using to_json():

Copy
db> 
SELECT to_json('{"hello": "world"}');
{'{"hello": "world"}'}
Copy
db> 
SELECT <json>'hello world';
{'"hello world"'}

Anything in EdgeDB can be cast into json:

Copy
db> 
SELECT <json>2019;
{'2019'}
Copy
db> 
SELECT <json>cal::to_local_date(datetime_current(), 'UTC');
{'"2019-04-02"'}

Any Object can be cast into json. This produces the same JSON value as the JSON serialization of that object. That is, the result is the same as the output of SELECT expression in JSON mode, including the type shape.

Copy
db> 
... 
... 
... 
... 
... 
... 
SELECT <json>(
    SELECT schema::Object {
        name,
        timestamp := cal::to_local_date(
            datetime_current(), 'UTC')
    }
    FILTER .name = 'std::bool');
{'{"name": "std::bool", "timestamp": "2019-04-02"}'}

JSON values can also be cast back into scalars. This casting is symmetrical meaning that if a scalar can be cast into JSON, only that particular JSON type can be cast back into that scalar:

  • JSON string can be cast into str. Casting uuid and date and time types to JSON results in a JSON string representing the original value. This means that it is also possible to cast a JSON string back into these types. The string value has to be properly formatted (much like in case of a str value being cast) or else the cast will raise an exception.

  • JSON number can be cast into any of the numeric types

  • JSON boolean can be cast into bool

  • JSON null is special since it can be cast into an {} of any type

  • JSON array can be cast into any valid EdgeDB array, so it must be homogeneous, and must not contain null

A regular tuple is converted into a JSON array when cast into json, whereas a named tuple is converted into a JSON object. These casts are not reversible, i.e. it is not possible to cast a JSON value directly into a tuple.

type
json
json

Arbitrary JSON data.

Any other type can be cast to and from JSON:

Copy
db> 
SELECT <json>42;
{'42'}
Copy
db> 
SELECT <bool>to_json('true');
{true}

Note that a json value can be cast into a str only when it is a JSON string. Therefore, while the following will work as expected:

Copy
db> 
SELECT <str>to_json('"something"');
{'something'}

The operation below (casting a JSON array of string ["a", "b", "c"] to a str) will result in an error:

Copy
db> 
SELECT <str>to_json('["a", "b", "c"]');
InternalServerError: expected json string, null; got json array

Use the to_json() and to_str() functions to dump or parse a json value to or from a str:

Copy
db> 
SELECT to_json('[1, "a"]');
{'[1, "a"]'}
Copy
db> 
SELECT to_str(<json>[1, 2]);
{'[1, 2]'}
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 ++ json
json ++ json -> json

JSON concatenation.

JSON arrays, objects and strings can be concatenated with JSON values of the same type into a new JSON value.

If you concatenate two JSON objects, you get a new object whose keys will be a union of the keys of the input objects. If a key is present in both objects, the value from the second object is taken.

Copy
db> 
SELECT to_json('[1, 2]') ++ to_json('[3]');
{'[1, 2, 3]'}
Copy
db> 
SELECT to_json('{"a": 1}') ++ to_json('{"b": 2}');
{'{"a": 1, "b": 2}'}
Copy
db> 
SELECT to_json('{"a": 1, "b": 2}') ++ to_json('{"b": 3}');
{'{"a": 1, "b": 3}'}
Copy
db> 
SELECT to_json('"123"') ++ to_json('"456"');
{'"123456"'}
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