Light
Dark
System

JSON Functions and Operators

json

JSON scalar type

json[i]

Accesses the element of the JSON string or array at a given index.

json[from:to]

Produces a JSON value comprising a portion of the existing JSON value.

json ++ json

Concatenates two JSON arrays, objects, or strings into one.

json[name]

Accesses an element of a JSON object given its key.

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

Comparison operators

to_json()

Returns a JSON value parsed from the given string.

to_str()

Render JSON value to a string.

json_get()

Returns a value from a JSON object or array given its path.

json_set()

Returns an updated JSON target with a new value.

json_array_unpack()

Returns the elements of a JSON array as a set of json.

json_object_unpack()

Returns the data in a JSON object as a set of key/value tuples.

json_typeof()

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

JSON in EdgeDB is a scalar type. This type doesn’t have its own literal, and instead can be obtained by either casting a value to the json type, or by using the to_json() function:

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

Any value in EdgeDB can be cast to a json type as well:

Copy
db> 
select <json>2019;
{Json("2019")}
Copy
db> 
select <json>cal::to_local_date(datetime_current(), 'UTC');
{Json("\"2022-11-21\"")}

Additionally, any Object in EdgeDB can be cast as a json type. This produces the same JSON value as the JSON-serialized result of that said object. Furthermore, this result will be the same as the output of a select expression in JSON mode, including the shape of that type:

Copy
db> 
... 
... 
... 
... 
... 
... 
select <json>(
    select schema::Object {
        name,
        timestamp := cal::to_local_date(
            datetime_current(), 'UTC')
    }
    filter .name = 'std::bool');
{Json("{\"name\": \"std::bool\", \"timestamp\": \"2022-11-21\"}")}

JSON values can also be cast back into scalars. Casting JSON is symmetrical meaning that, if a scalar value can be cast into JSON, a compatible JSON value can be cast into a scalar of that type. Some scalar types will have specific conditions for casting:

  • JSON strings can be cast to a str type. Casting uuid and date/time types to JSON results in a JSON string representing its original value. This means it is also possible to cast a JSON string back to those types. The value of the UUID or datetime string must be properly formatted to successfully cast from JSON, otherwise EdgeDB will raise an exception.

  • JSON numbers can be cast to any numeric type.

  • JSON booleans can be cast to a bool type.

  • JSON null is unique because it can be cast to an empty set ({}) of any type.

  • JSON arrays can be cast to any valid array type, as long as the JSON array is homogeneous, does not contain null as an element of the array, and does not contain another array.

A named tuple is converted into a JSON object when cast as a json while a standard tuple is converted into a JSON array. Unlike other casts to JSON, tuple casts to JSON 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;
{Json("42")}
Copy
db> 
select <bool>to_json('true');
{true}

A json value can also be cast as a str type, but only when recognized as a JSON string:

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

Casting a JSON array of strings (["a", "b", "c"]) to a str will result in an error:

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

Instead, use the to_str() function to dump a JSON value to a str value. Use the to_json() function to parse a JSON string to a json value:

Copy
db> 
select to_json('[1, "a"]');
{Json("[1, \"a\"]")}
Copy
db> 
select to_str(<json>[1, 2]);
{'[1, 2]'}
operator
json[i]
json [ int64 ] -> json

Accesses the element of the JSON string or array at a given index.

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

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

This will raise an exception if the specified index is not valid for the base JSON value. To access an index that is potentially out of bounds, use json_get().

operator
json[from:to]
json [ int64 : int64 ] -> json

Produces a JSON value comprising a portion of the existing JSON value.

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];
{Json("\"he\"")}
Copy
db> 
select <json>'hello'[2:];
{Json("\"llo\"")}
Copy
db> 
select to_json('[1, 2, 3]')[0:2];
{Json("[1, 2]")}
Copy
db> 
select to_json('[1, 2, 3]')[2:];
{Json("[3]")}
Copy
db> 
select to_json('[1, 2, 3]')[:1];
{Json("[1]")}
Copy
db> 
select to_json('[1, 2, 3]')[:-2];
{Json("[1]")}
operator
json ++ json
json ++ json -> json

Concatenates two JSON arrays, objects, or strings into one.

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]');
{Json("[1, 2, 3]")}
Copy
db> 
select to_json('{"a": 1}') ++ to_json('{"b": 2}');
{Json("{\"a\": 1, \"b\": 2}")}
Copy
db> 
select to_json('{"a": 1, "b": 2}') ++ to_json('{"b": 3}');
{Json("{\"a\": 1, \"b\": 3}")}
Copy
db> 
select to_json('"123"') ++ to_json('"456"');
{Json("\"123456\"")}
operator
json[name]
json [ str ] -> json

Accesses an element of a JSON object given its key.

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

Copy
db> 
select to_json('{"a": 2, "b": 5}')['b'];
{Json("5")}
Copy
db> 
... 
... 
... 
... 
select j := <json>(schema::Type {
    name,
    timestamp := cal::to_local_date(datetime_current(), 'UTC')
})
filter j['name'] = <json>'std::bool';
{Json("{\"name\": \"std::bool\", \"timestamp\": \"2022-11-21\"}")}

This will raise an exception if the specified field does not exist for the base JSON value. To access an index that is potentially out of bounds, use json_get().

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

Returns a JSON value parsed from the given string.

Copy
db> 
select to_json('[1, "hello", null]');
{Json("[1, \"hello\", null]")}
Copy
db> 
select to_json('{"hello": "world"}');
{Json("{\"hello\": \"world\"}")}
function
json_array_unpack()
std::json_array_unpack(json: json) -> set of json

Returns the elements of a JSON array as a set of json.

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

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

Copy
db> 
select json_array_unpack(to_json('[1, "a"]'));
{Json("1"), Json("\"a\"")}
function
json_get()
std::json_get(json: json, variadic path: str) -> optional json

Returns a value from a JSON object or array given its path.

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:

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

This is useful when certain structure of JSON data is assumed, but cannot be reliably guaranteed. 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', '2');
{}

If you want to supply your own default for the case where the path cannot be followed, you can do so using the coalesce operator:

Copy
db> 
... 
... 
... 
... 
select json_get(to_json('{
    "q": 1,
    "w": [2, "foo"],
    "e": true
}'), 'w', '2') ?? <json>'mydefault';
{Json("\"mydefault\"")}
function
json_set()
std::json_set( target: json, variadic path: str, named only value: optional json, named only create_if_missing: bool = true, named only empty_treatment: JsonEmpty = JsonEmpty.ReturnEmpty) -> optional json

Returns an updated JSON target with a new value.

This function is only available in EdgeDB 2.0 or later.

Copy
db> 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'a',
  value := <json>true,
);
{Json("{\"a\": true, \"b\": 20}")}
Copy
db> 
... 
... 
... 
... 
select json_set(
  to_json('{"a": {"b": {}}}'),
  'a', 'b', 'c',
  value := <json>42,
);
{Json("{\"a\": {\"b\": {\"c\": 42}}}")}

If create_if_missing is set to false, a new path for the value won’t be created:

Copy
db> 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'с',
  value := <json>42,
);
{Json("{\"a\": 10, \"b\": 20, \"с\": 42}")}
Copy
db> 
... 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'с',
  value := <json>42,
  create_if_missing := false,
);
{Json("{\"a\": 10, \"b\": 20}")}

The empty_treatment parameter defines the behavior of the function if an empty set is passed as new_value. This parameter can take these values:

  • ReturnEmpty: return empty set, default

  • ReturnTarget: return target unmodified

  • Error: raise an InvalidValueError

  • UseNull: use a null JSON value

  • DeleteKey: delete the object key

Copy
db> 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'a',
  value := <json>{}
);
{}
Copy
db> 
... 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'a',
  value := <json>{},
  empty_treatment := JsonEmpty.ReturnTarget,
);
{Json("{\"a\": 10, \"b\": 20}")}
Copy
db> 
... 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'a',
  value := <json>{},
  empty_treatment := JsonEmpty.Error,
);
InvalidValueError: invalid empty JSON value
Copy
db> 
... 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'a',
  value := <json>{},
  empty_treatment := JsonEmpty.UseNull,
);
{Json("{\"a\": null, \"b\": 20}")}
Copy
db> 
... 
... 
... 
... 
... 
select json_set(
  to_json('{"a": 10, "b": 20}'),
  'a',
  value := <json>{},
  empty_treatment := JsonEmpty.DeleteKey,
);
{Json("{\"b\": 20}")}
function
json_object_unpack()
std::json_object_unpack(json: json) -> set of tuple<str, json>

Returns the data in a JSON object as a set of key/value tuples.

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

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

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

Possible return values are: 'object', 'array', 'string', 'number', 'boolean', or '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