json.rst

JSON

edb-alt-title

JSON Functions and Operators

:eqljson	JSON scalar type
:eqljson[i] <jsonidx>	:eqljsonidx
:eqljson[from:to] <jsonslice>	:eqljsonslice
:eqljson ++ json <jsonplus>	:eqljsonplus
:eqljson[name] <jsonobjdest>	:eqljsonobjdest
:eql= <eq> :eql\!= <neq> :eql?= <coaleq> :eql?!= <coalneq> :eql\< <lt> :eql\> <gt> :eql\<= <lteq> :eql\>= <gteq>	Comparison operators
:eqlto_json	:eqlto_json
:eqlto_str	Render JSON value to a string.
:eqljson_get	:eqljson_get
:eqljson_set	:eqljson_set
:eqljson_array_unpack	:eqljson_array_unpack
:eqljson_object_pack	:eqljson_object_pack
:eqljson_object_unpack	:eqljson_object_unpack
:eqljson_typeof	:eqljson_typeof

Constructing JSON Values

JSON in EdgeDB is a scalar type <ref_datamodel_scalar_types>. This type doesn't have its own literal, and instead can be obtained by either casting a value to the :eqljson type, or by using the :eqlto_json function:

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

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

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

3.0

The :eqljson_object_pack function provides one more way to construct JSON. It constructs a JSON object from an array of key/value tuples:

db> select json_object_pack({("hello", <json>"world")});
{Json("{\"hello\": \"world\"}")}

Additionally, any :eqlObject in EdgeDB can be cast as a :eqljson 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 :eqlselect expression <select> in JSON mode, including the shape of that type:

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 :eqlstr type. Casting :eqluuid and date/time <ref_std_datetime> 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 <ref_std_numeric>.
• JSON booleans can be cast to a :eqlbool 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 :eqltuple is converted into a JSON object when cast as a :eqljson while a standard :eqltuple is converted into a JSON array.

---

---

---

---

---

---

---

---

index

safe navigation

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:

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:

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 :eqlcoalesce operator:

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

---

-> optional json

2.0

Returns an updated JSON target with a new value.

db> select json_set(
...   to_json('{"a": 10, "b": 20}'),
...   'a',
...   value := <json>true,
... );
{Json("{\"a\": true, \"b\": 20}")}
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:

db> select json_set(
...   to_json('{"a": 10, "b": 20}'),
...   'с',
...   value := <json>42,
... );
{Json("{\"a\": 10, \"b\": 20, \"с\": 42}")}
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

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

---

3.0

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

db> select json_object_pack({
...     ("foo", to_json("1")),
...     ("bar", to_json("null")),
...     ("baz", to_json("[]"))
... });
{Json("{\"bar\": null, \"baz\": [], \"foo\": 1}")}

If the key/value tuples being packed have common keys, the last value for each key will make the final object.

db> select json_object_pack({
...     ("hello", <json>"world"),
...     ("hello", <json>true)
... });
{Json("{\"hello\": true}")}

---

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.

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

---