:meta-keywords: coradb json, json functions, database json, JSON_TABLE
:meta-description: CoraDB functions that create, query and modify JSON data.
:tocdepth: 3
.. _json-fn:
*********************************
JSON functions
*********************************
.. _fn-json-intro:
Introduction to JSON functions
===================================
The functions described in this section perform operations on JSON data.
All supported JSON functions can be found in next table:
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \adjacency-list` | \array` | \array-append` | \array-insert` |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \arrayagg` | \arrow` | \contains` | \contains-path` |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \depth` | \double-arrow` | \edge-\ | \extract` |
| | | \properties` | |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \graph` | \graph-heap-\ | \info` | \insert` |
| | \memory-usage` | | |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \keys` | \length` | \merge` | \merge-patch` |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \merge-preserve` | \node-\ | \object` | \objectagg` |
| | \properties` | | |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \pretty` | \properties` | \quote` | \remove` |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \replace` | \search` | \set` | \table` |
+------------------+------------------+------------------+------------------+
| :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ | :ref:`fn-json-\ |
| \topology-\ | \type` | \unquote` | \valid` |
| \record` | | | |
+------------------+------------------+------------------+------------------+
They have in common several types of input arguments:
- *json_doc*: a JSON or string that is parsed as JSON
- *val*: a JSON or a value that can be interpreted as one of supported JSON
scalar types
- *json key*: a string as key name
- *json path/pointer*: a string that follows the rules explained in
:ref:`json-path` and :ref:`json-pointer`.
.. note::
UTF8 is expected to be the codeset of JSON functions string arguments.
Inputs with different codesets are implicitly converted to UTF8. One
consequence is that searching a case insensitive collation string with a
codeset other than UTF8 may not provide expected results.
The next table shows the differences between *json_doc* and *val* when
accepting input arguments:
+-------------------+-----------------------------+---------------------------+
| Input type | *json_doc* | *val* |
+===================+=============================+===========================+
| JSON | Input is unchanged | Input is unchanged |
+-------------------+-----------------------------+---------------------------+
| String | JSON value is parsed from | Input is converted to |
| | input | JSON STRING |
+-------------------+-----------------------------+---------------------------+
| Short, Integer | Conversion error | Input is converted to |
| | | JSON INTEGER |
+-------------------+-----------------------------+---------------------------+
| Bigint | Conversion error | Input is converted to |
| | | JSON BIGINT |
+-------------------+-----------------------------+---------------------------+
| Float, Double, | Conversion error | Input is converted to |
| Numeric | | JSON DOUBLE |
+-------------------+-----------------------------+---------------------------+
| NULL | NULL | Input is converted to |
| | | JSON_NULL |
+-------------------+-----------------------------+---------------------------+
| Other | Conversion error | Conversion error |
+-------------------+-----------------------------+---------------------------+
.. _fn-json-array:
JSON_ARRAY
===================================
.. function:: JSON_ARRAY ([val1 [ , val2] ...])
The **JSON_ARRAY** function returns a json array containing the given list (possibly empty) of values.
.. code-block:: sql
SELECT JSON_ARRAY();
::
json_array()
======================
[]
.. code-block:: sql
SELECT JSON_ARRAY(1, '1', json '{"a":4}', json '[1,2,3]');
::
json_array(1, '1', json '{"a":4}', json '[1,2,3]')
======================
[1,"1",{"a":4},[1,2,3]]
.. _fn-json-object:
JSON_OBJECT
===================================
.. function:: JSON_OBJECT ([key1, val1 [ , key2, val2] ...])
The **JSON_OBJECT** function returns a json object containing the given list (possibly empty) of key-value pairs.
.. code-block:: sql
SELECT JSON_OBJECT();
::
json_object()
======================
{}
.. code-block:: sql
SELECT JSON_OBJECT('a', 1, 'b', '1', 'c', json '{"a":4}', 'd', json '[1,2,3]');
::
json_object('a', 1, 'b', '1', 'c', json '{"a":4}', 'd', json '[1,2,3]')
======================
{"a":1,"b":"1","c":{"a":4},"d":[1,2,3]}
.. _fn-json-keys:
JSON_KEYS
===================================
.. function:: JSON_KEYS (json_doc [ , json path])
The **JSON_KEYS** function returns a json array of all the object keys of the json object at the given path.
Json null is returned if the path addresses a json element that is not a json object.
If json path argument is missing, the keys are gathered from json root element.
An error occurs if *json path* does not exist. Returns **NULL** if *json_doc* argument is **NULL**.
.. code-block:: sql
SELECT JSON_KEYS('{}');
::
json_keys('{}')
======================
[]
.. code-block:: sql
SELECT JSON_KEYS('"non-object"');
::
json_keys('"non-object"')
======================
null
.. code-block:: sql
SELECT JSON_KEYS('{"a":1, "b":2, "c":{"d":1}}');
::
json_keys('{"a":1, "b":2, "c":{"d":1}}')
======================
["a","b","c"]
.. _fn-json-depth:
JSON_DEPTH
===================================
.. function:: JSON_DEPTH (json_doc)
The **JSON_DEPTH** function returns the maximum depth of the json.
Depth count starts at 1. The depth level is increased by one by non-empty json arrays or by non-empty json objects.
Returns **NULL** if argument is **NULL**.
.. code-block:: sql
SELECT JSON_DEPTH('"scalar"');
::
json_depth('"scalar"')
======================
1
.. code-block:: sql
SELECT JSON_DEPTH('[{"a":4}, 2]');
::
json_depth('[{"a":4}, 2]')
======================
3
Example of a deeper json:
.. code-block:: sql
SELECT JSON_DEPTH('[{"a":[1,2,3,{"k":[4,5]}]},2,3,4,5,6,7]');
::
json_depth('[{"a":[1,2,3,{"k":[4,5]}]},2,3,4,5,6,7]')
======================
6
.. _fn-json-length:
JSON_LENGTH
===================================
.. function:: JSON_LENGTH (json_doc [ , json path])
The **JSON_LENGTH** function returns the length of the json element at the given path.
If no path argument is given, the returned value is the length of the root json element.
Returns **NULL** if any argument is **NULL** or if no element exists at the given path.
.. code-block:: sql
SELECT JSON_LENGTH('"scalar"');
::
json_length('"scalar"')
======================
1
.. code-block:: sql
SELECT JSON_LENGTH('[{"a":4}, 2]', '$.a');
::
json_length('[{"a":4}, 2]', '$.a')
======================
NULL
.. code-block:: sql
SELECT JSON_LENGTH('[2, {"a":4, "b":4, "c":4}]', '$[1]');
::
json_length('[2, {"a":4, "b":4, "c":4}]', '$[1]')
======================
3
.. code-block:: sql
SELECT JSON_LENGTH('[{"a":[1,2,3,{"k":[4,5,6,7,8]}]},2]');
::
json_length('[{"a":[1,2,3,{"k":[4,5,6,7,8]}]},2]')
======================
2
.. _fn-json-valid:
JSON_VALID
===================================
.. function:: JSON_VALID (val)
The **JSON_VALID** function returns 1 if the given *val* argument is a valid json_doc, 0 otherwise.
Returns **NULL** if argument is **NULL**.
.. code-block:: sql
SELECT JSON_VALID('[{"a":4}, 2]');
1
SELECT JSON_VALID('{"wrong json object":');
0
.. _fn-json-type:
JSON_TYPE
===================================
.. function:: JSON_TYPE (json_doc)
The **JSON_TYPE** function returns the type of the *json_doc* argument as a string.
.. code-block:: sql
SELECT JSON_TYPE ('[{"a":4}, 2]');
'JSON_ARRAY'
SELECT JSON_TYPE ('{"a":4}');
'JSON_OBJECT'
SELECT JSON_TYPE ('"aaa"');
'STRING'
.. _fn-json-quote:
JSON_QUOTE
===================================
.. function:: JSON_QUOTE (str)
Escapes quotes and special characters and surrounds the resulting string in quotes. Returns result as a json_string.
Returns **NULL** if *str* argument is **NULL**.
.. code-block:: sql
SELECT JSON_QUOTE ('simple');
::
json_unquote('simple')
======================
'"simple"'
.. code-block:: sql
SELECT JSON_QUOTE ('"');
::
json_unquote('"')
======================
'"\""'
.. _fn-json-unquote:
JSON_UNQUOTE
===================================
.. function:: JSON_UNQUOTE (json_doc)
Unquotes a json_value's json string and returns the resulting string.
Returns **NULL** if *json_doc* argument is **NULL**.
.. code-block:: sql
SELECT JSON_UNQUOTE ('"\\u0032"');
::
json_unquote('"\u0032"')
======================
'2'
.. code-block:: sql
SELECT JSON_UNQUOTE ('"\\""');
::
json_unquote('"\""')
======================
'"'
.. _fn-json-pretty:
JSON_PRETTY
===================================
.. function:: JSON_PRETTY (json_doc)
Returns a string containing the *json_doc* pretty-printed.
Returns **NULL** if *json_doc* argument is **NULL**.
.. code-block:: sql
SELECT JSON_PRETTY('[{"a":"val1", "b":"val2", "c": [1, "elem2", 3, 4, {"key":"val"}]}]');
::
json_pretty('[{"a":"val1", "b":"val2", "c": [1, "elem2", 3, 4, {"key":"val"}]}]')
======================
'[
{
"a": "val1",
"b": "val2",
"c": [
1,
"elem2",
3,
4,
{
"key": "val"
}
]
}
]'
.. _fn-json-search:
JSON_SEARCH
===================================
.. function:: JSON_SEARCH (json_doc, one/all, search_str [, escape_char [, json path] ...])
Returns a json array of json paths or a single json path which contain json strings matching the given *search_str*.
The matching is performed by applying the **LIKE** operator on internal json strings and *search_str*. Same rules apply for the *escape_char* and *search_str* of **JSON_SEARCH** as for their counter-parts from the **LIKE** operator.
For further description of **LIKE**-related arguments rules refer to :ref:`like-expr`.
Using 'one' as one/all argument will cause the **JSON_SEARCH** to stop after the first match is found.
On the other hand, 'all' will force **JSON_SEARCH** to gather all paths matching the given *search_str*.
The given json paths determine filters on the returned paths, the resulting json paths's prefixes need to match at least one given json path argument.
If no json path argument is given, **JSON_SEARCH** will execute the search starting from the root element.
.. code-block:: sql
SELECT JSON_SEARCH('{"a":["a","b"],"b":"a","c":"a"}', 'one', 'a');
::
json_search('{"a":["a","b"],"b":"a","c":"a"}', 'one', 'a')
======================
"$.a[0]"
.. code-block:: sql
SELECT JSON_SEARCH('{"a":["a","b"],"b":"a","c":"a"}', 'all', 'a');
::
json_search('{"a":["a","b"],"b":"a","c":"a"}', 'all', 'a')
======================
"["$.a[0]","$.b","$.c"]"
.. code-block:: sql
SELECT JSON_SEARCH('{"a":["a","b"],"b":"a","c":"a"}', 'all', 'a', NULL, '$.a', '$.b');
::
json_search('{"a":["a","b"],"b":"a","c":"a"}', 'all', 'a', null, '$.a', '$.b')
======================
"["$.a[0]","$.b"]"
Wildcards can be used to define path filters as more general formats.
Accepting only json paths that start with object key identifier:
.. code-block:: sql
SELECT JSON_SEARCH('{"a":["a","b"],"b":"a","c":"a"}', 'all', 'a', NULL, '$.*');
::
json_search('{"a":["a","b"],"b":"a","c":"a"}', 'all', 'a', null, '$.*')
======================
"["$.a[0]","$.b","$.c"]"
Accepting only json paths that start with object key identifier and follow immediately with a json array index will filter out '$.b', '$.d.e[0]' matches:
.. code-block:: sql
SELECT JSON_SEARCH('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', 'all', 'a', NULL, '$.*[*]');
::
json_search('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', 'all', 'a', null, '$.*[*]')
======================
"["$.a[0]","$.c[0]"]"
Accepting any paths that contain json array indexes will filter out '$.b'
.. code-block:: sql
SELECT JSON_SEARCH('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', 'all', 'a', NULL, '$**[*]');
::
json_search('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', 'all', 'a', null, '$**[*]')
======================
"["$.a[0]","$.c[0]","$.d.e[0]"]"
.. _fn-json-extract:
JSON_EXTRACT
===================================
.. function:: JSON_EXTRACT (json_doc, json path [, json path] ...)
Returns json elements from the *json_doc*, that are addressed by the given paths.
If json path arguments contain wildcards, all elements that are addressed by a path compatible with the wildcards-containing json path are gathered in a resulting json array.
A single json element is returned if no wildcards are used in the given json paths and a single element is found, otherwise the json elements found are wrapped in a json array.
Raises an error if a json path is **NULL** or invalid or if *json_doc* argument is invalid.
Returns **NULL** if no elements are found or if json_doc is **NULL**.
.. code-block:: sql
SELECT JSON_EXTRACT('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$.a');
::
json_extract('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$.a')
======================
"["a","b"]" -- at '$.a' we have the json array ["a","b"]
.. code-block:: sql
SELECT JSON_EXTRACT('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$.a[*]');
::
json_extract('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$.a[*]')
======================
"["a","b"]" -- '$.a[0]' and '$.a[1]' wrapped in a json array, forming ["a","b"]
Changing '.a' from previous query with '.*' wildcards will also match '$.c[0]'. This will match any json path that is exactly an object key identifier followed by an array index.
.. code-block:: sql
SELECT JSON_EXTRACT('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$.*[*]');
::
json_extract('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$.*[*]')
======================
"["a","b","a"]"
The following json path will match all json paths that end with a json array index (matches all previous matched paths and, in addition, '$.d.e[0]') :
.. code-block:: sql
SELECT JSON_EXTRACT('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$**[*]');
::
json_extract('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$**[*]')
======================
"["a","b","a","a"]"
.. code-block:: sql
SELECT JSON_EXTRACT('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$.d**[*]');
::
json_extract('{"a":["a","b"],"b":"a","c":["a"], "d":{"e":["a"]}}', '$d**[*]')
======================
"["a"]" -- '$.d.e[0]' is the only path matching the given argument path family - paths that start with '.d' and end with an array index
.. _fn-json-arrow:
->
===================================
.. function:: json_doc -> json path
Alias operator for **JSON_EXTRACT** with two arguments, having the *json_doc* argument constrained to be a column.
Raises an error if the json path is **NULL** or invalid.
Returns **NULL** if it is applied on a **NULL** *json_doc* argument.
.. code-block:: sql
CREATE TABLE tj (a json);
INSERT INTO tj values ('{"a":1}'), ('{"a":2}'), ('{"a":3}'), (NULL);
SELECT a->'$.a' from tj;
::
json_extract(a, '$.a')
======================
1
2
3
NULL
.. _fn-json-double-arrow:
->>
===================================
.. function:: json_doc ->> json path
Alias for **JSON_UNQUOTE** (json_doc->json path). Operator can be applied only on *json_doc* arguments that are columns.
Raises an error if the json path is **NULL** or invalid.
Returns **NULL** if it is applied on a **NULL** *json_doc* argument.
.. code-block:: sql
CREATE TABLE tj (a json);
INSERT INTO tj values ('{"a":1}'), ('{"a":2}'), ('{"a":3}'), (NULL);
SELECT a->>'$.a' from tj;
::
json_unquote(json_extract(a, '$.a'))
======================
'1'
'2'
'3'
NULL
.. _fn-json-contains-path:
JSON_CONTAINS_PATH
===================================
.. function:: JSON_CONTAINS_PATH (json_doc, one/all, json path [, json path] ...)
The **JSON_CONTAINS_PATH** function verifies whether the given paths exist inside the *json_doc*.
When one/all argument is 'all', all given paths must exist to return 1. Returns 0 otherwise.
When one/all argument is 'one', it returns 1 if any given path exists. Returns 0 otherwise.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid.
.. code-block:: sql
SELECT JSON_CONTAINS_PATH ('[{"0":0},1,"2",{"three":3}]', 'all', '$[0]', '$[0]."0"', '$[1]', '$[2]', '$[3]');
::
json_contains_path('[{"0":0},1,"2",{"three":3}]', 'all', '$[0]', '$[0]."0"', '$[1]', '$[2]', '$[3]')
======================================================================================================
1
.. code-block:: sql
SELECT JSON_CONTAINS_PATH ('[{"0":0},1,"2",{"three":3}]', 'all', '$[0]', '$[0]."0"', '$[1]', '$[2]', '$[3]', '$.inexistent');
::
json_contains_path('[{"0":0},1,"2",{"three":3}]', 'all', '$[0]', '$[0]."0"', '$[1]', '$[2]', '$[3]', '$.inexistent')
======================================================================================================================
0
The **JSON_CONTAINS_PATH** function supports wildcards inside json paths.
.. code-block:: sql
SELECT JSON_CONTAINS_PATH ('[{"0":0},1,"2",{"three":3}]', 'one', '$.inexistent', '$[*]."three"');
::
json_contains_path('[{"0":0},1,"2",{"three":3}]', 'one', '$.inexistent', '$[*]."three"')
==========================================================================
1
.. _fn-json-contains:
JSON_CONTAINS
===================================
.. function:: JSON_CONTAINS (json_doc doc1, json_doc doc2 [, json path])
The **JSON_CONTAINS** function verifies whether the *doc2* is contained inside the *doc1* at the optionally specified path.
A json element contains another json element if the following recursive rules are satisfied:
- A json scalar contains another json scalar if they have the same type (their **JSON_TYPE** () are equal) and are equal. As an exception, json integer can be compared and equal to json double (even if their **JSON_TYPE** () evaluation are different).
- A json array contains a json scalar or a json object if any of json array's elements contains the json_nonarray.
- A json array contains another json array if all the second json array's elements are contained in the first json array.
- A json object contains another json object if, for every (*key2*, *value2*) pair in the second object, there exists a (*key1*, *value1*) pair in the first object with *key1* = *key2* and *value2* contained in *value1*.
- Otherwise the json element is not contained.
Returns whether *doc2* is contained in root json element of *doc1* if no json path argument is given.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid.
.. code-block:: sql
SELECT JSON_CONTAINS ('"simple"','"simple"');
::
json_contains('"simple"', '"simple"')
=======================================
1
.. code-block:: sql
SELECT JSON_CONTAINS ('["a", "b"]','"b"');
::
json_contains('["a", "b"]', '"b"')
====================================
1
.. code-block:: sql
SELECT JSON_CONTAINS ('["a", "b1", ["a", "b2"]]','["b1", "b2"]');
::
json_contains('["a", "b1", ["a", "b2"]]','["b1", "b2"]')
==========================================================
1
.. code-block:: sql
SELECT JSON_CONTAINS ('{"k1":["a", "b1"], "k2": ["a", "b2"]}','{"k1":"b1", "k2":"b2"}');
::
json_contains('{"k1":["a", "b1"], "k2": ["a", "b2"]}','{"k1":"b1", "k2":"b2"}')
=================================================================================
1
Note that json objects do not check containment the same way json arrays do. It is impossible to have a json element that is not a descendent of a json object contained in a sub-element of a json object.
.. code-block:: sql
SELECT JSON_CONTAINS ('["a", "b1", ["a", {"k":"b2"}]]','["b1", "b2"]');
::
json_contains('["a", "b1", ["a", {"k":"b2"}]]','["b1", "b2"]')
================================================================
0
.. code-block:: sql
SELECT JSON_CONTAINS ('["a", "b1", ["a", {"k":["b2"]}]]','["b1", {"k":"b2"}]');
::
json_contains('["a", "b1", ["a", {"k":["b2"]}]]','["b1", {"k":"b2"}]')
========================================================================
1
.. _fn-json-merge-patch:
JSON_MERGE_PATCH
===================================
.. function:: JSON_MERGE_PATCH (json_doc, json_doc [, json_doc] ...)
The **JSON_MERGE_PATCH** function merges two or more json docs and returns the resulting merged json. **JSON_MERGE_PATCH** differs from **JSON_MERGE_PRESERVE** in that it will take the second argument when encountering merging conflicts. **JSON_MERGE_PATCH** is compliant with
`RFC 7396 `_.
The merging of two json documents is performed with the following rules, recursively:
- when two non-object jsons are merged, the result of the merge is the second value.
- when a non-object json is merged with a json object, the result is the merge of an empty object with the second merging argument.
- when two objects are merged, the resulting object consists of the following members:
- All members from the first object that have no corresponding member with the same key in the second object.
- All members from the second object that have no corresponding members with equal keys in the first object, having values not null. Members with null values from second object are ignored.
- One member for each member in the first object that has a corresponding non-null valued member in the second object with the same key. Same key members that appear in both objects and the second object's member value is null, are ignored. The values of these pairs become the results of merging operations performed on the values of the members from the first and second object.
Merge operations are executed serially when there are more than two arguments: the result of merging first two arguments is merged with third, this result is then merged with fourth and so on.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is not valid.
.. code-block:: sql
SELECT JSON_MERGE_PATCH ('["a","b","c"]', '"scalar"');
::
json_merge_patch('["a","b","c"]', '"scalar"')
======================
"scalar"
The exception to the merge-patching, when the first argument is non-object and the second is an object. A merge operation is performed between an empty object and the second object argument.
.. code-block:: sql
SELECT JSON_MERGE_PATCH ('["a"]', '{"a":null}');
::
json_merge_patch('["a"]', '{"a":null}')
======================
{}
Objects merging example, exemplifying the described object merging rules:
.. code-block:: sql
SELECT JSON_MERGE_PATCH ('{"a":null,"c":["elem"]}','{"b":null,"c":{"k":null},"d":"elem"}');
::
json_merge_patch('{"a":null,"c":["elem"]}', '{"b":null,"c":{"k":null},"d":"elem"}')
======================
{"a":null,"c":{},"d":"elem"}
.. _fn-json-merge-preserve:
JSON_MERGE_PRESERVE
===================================
.. function:: JSON_MERGE_PRESERVE (json_doc, json_doc [, json_doc] ...)
The **JSON_MERGE_PRESERVE** function merges two or more json docs and returns the resulting merged json. **JSON_MERGE_PRESERVE** differs from **JSON_MERGE_PATCH** in that it preserves both json elements on merging conflicts.
The merging of two json documents is performed after the following rules, recursively:
- when two json arrays are merged, they are concatenated.
- when two non-array (scalar/object) json elements are merged and at most one of them is a json object, the result is an array containing the two json elements.
- when a non-array json element is merged with a json array, the non-array is wrapped as a single element json array and then merged with the json array according to json array merging rules.
- when two json objects are merged, all pairs that do not have a corresponding pair in the other json object are preserved. For matching keys, the values are always merged by applying the rules recursively.
Merge operations are executed serially when there are more than two arguments: the result of merging first two arguments is merged with third, this result is then merged with fourth and so on.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is not valid.
.. code-block:: sql
SELECT JSON_MERGE_PRESERVE ('"a"', '"b"');
::
json_merge('"a"', '"b"')
======================
["a","b"]
.. code-block:: sql
SELECT JSON_MERGE_PRESERVE ('["a","b","c"]', '"scalar"');
::
json_merge('["a","b","c"]', '"scalar"')
======================
["a","b","c","scalar"]
**JSON_MERGE_PRESERVE**, as opposed to **JSON_MERGE_PATCH**, will not drop and patch first argument's elements during merges and will gather them together.
.. code-block:: sql
SELECT JSON_MERGE_PRESERVE ('{"a":null,"c":["elem"]}','{"b":null,"c":{"k":null},"d":"elem"}');
::
json_merge('{"a":null,"c":["elem"]}','{"b":null,"c":{"k":null},"d":"elem"}')
======================
{"a":null,"c":["elem",{"k":null}],"b":null,"d":"elem"}
.. _fn-json-merge:
JSON_MERGE
===================================
.. function:: JSON_MERGE (json_doc, json_doc [, json_doc] ...)
**JSON_MERGE** is an alias for **JSON_MERGE_PRESERVE**.
.. _fn-json-array-append:
JSON_ARRAY_APPEND
===================================
.. function:: JSON_ARRAY_APPEND (json_doc, json path, json_val [, json path, json_val] ...)
The **JSON_ARRAY_APPEND** function returns a modified copy of the first argument. For each given <*json path*, *json_val*> pair, the function appends the value to the json array addressed by the corresponding path.
The (*json path*, *json_val*) pairs are evaluated one by one, from left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.
If the json path points to a json array inside the *json_doc*, the *json_val* is appended at the end of the array.
If the json path points to a non-array json element, the non-array gets wrapped as a single element json array containing the referred non-array element followed by the appending of the given *json_val*.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid.
.. code-block:: sql
SELECT JSON_ARRAY_APPEND ('{"a":[1,2]}','$.a','b');
::
json_array_append('{"a":[1,2]}', '$.a', 'b')
======================
{"a":[1,2,"b"]}
.. code-block:: sql
SELECT JSON_ARRAY_APPEND ('{"a":1}','$.a','b');
::
json_array_append('{"a":1}', '$.a', 'b')
======================
{"a":[1,"b"]}
.. code-block:: sql
SELECT JSON_ARRAY_APPEND ('{"a":[1,2]}', '$.a[0]', '1');
::
json_array_append('{"a":[1,2]}', '$.a[0]', '1')
======================
{"a":[[1,"1"],2]}
.. _fn-json-array-insert:
JSON_ARRAY_INSERT
===================================
.. function:: JSON_ARRAY_INSERT (json_doc, json path, json_val [, json path, json_val] ...)
The **JSON_ARRAY_INSERT** function returns a modified copy of the first argument. For each given <*json path*, *json_val*> pair, the function inserts the value in the json array addressed by the corresponding path.
The (*json path*, *json_val*) pairs are evaluated one by one, from left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.
The rules of the **JSON_ARRAY_INSERT** operation are the following:
- if a json path addresses an element of a json_array, the given *json_val* is inserted at the specified index, shifting any following elements to the right.
- if the json path points to an array index after the end of an array, the array is filled with nulls after end of the array until the specified index and the json_val is inserted at the specified index.
- if the json path does not exist inside the *json_doc*, the last token of the json path is an array index and the json path without the last array index token would have pointed to an element inside the *json_doc*, the element found by the stripped json path is replaced with single element json array and the **JSON_ARRAY_INSERT** operation is performed with the original json path.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid or if a *json_path* does not address a cell of an array inside the *json_doc*.
.. code-block:: sql
SELECT JSON_ARRAY_INSERT ('[0,1,2]', '$[0]', '1');
::
json_array_insert('[0,1,2]', '$[0]', '1')
======================
["1",0,1,2]
.. code-block:: sql
SELECT JSON_ARRAY_INSERT ('[0,1,2]', '$[5]', '1');
::
json_array_insert('[0,1,2]', '$[5]', '1')
======================
[0,1,2,null,null,"1"]
Examples for **JSON_ARRAY_INSERT's** third rule.
.. code-block:: sql
SELECT JSON_ARRAY_INSERT ('{"a":4}', '$[5]', '1');
::
json_array_insert('{"a":4}', '$[5]', '1')
======================
[{"a":4},null,null,null,null,"1"]
.. code-block:: sql
SELECT JSON_ARRAY_INSERT ('"a"', '$[5]', '1');
::
json_array_insert('"a"', '$[5]', '1')
======================
["a",null,null,null,null,"1"]
.. _fn-json-insert:
JSON_INSERT
===================================
.. function:: JSON_INSERT (json_doc, json path, json_val [, json path, json_val] ...)
The **JSON_INSERT** function returns a modified copy of the first argument. For each given <*json path*, *json_val*> pair, the function inserts the value if no other value exists at the corresponding path.
The insertion rules for **JSON_INSERT** are the following:
The *json_val* is inserted if the json path addresses one of the following json values inside the *json_doc*:
- An inexistent object member of an existing json object. A (*key*, *value*) pair is added to the json object with the key being json path's last element and the value being the *json_val*.
- An array index past of an existing json array's end. The array is filled with nulls after the initial end of the array and the *json_val* is inserted at the specified index.
The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid.
Paths to existing elements inside the *json_doc* are ignored:
.. code-block:: sql
SELECT JSON_INSERT ('{"a":1}','$.a','b');
::
json_insert('{"a":1}', '$.a', 'b')
======================
{"a":1}
.. code-block:: sql
SELECT JSON_INSERT ('{"a":1}','$.b','1');
::
json_insert('{"a":1}', '$.b', '1')
======================
{"a":1,"b":"1"}
.. code-block:: sql
SELECT JSON_INSERT ('[0,1,2]','$[4]','1');
::
json_insert('[0,1,2]', '$[4]', '1')
======================
[0,1,2,null,"1"]
.. _fn-json-set:
JSON_SET
===================================
.. function:: JSON_SET (json_doc, json path, json_val [, json path, json_val] ...)
The **JSON_SET** function returns a modified copy of the first argument. For each given <*json path*, *json_val*> pair, the function inserts or replaces the value at the corresponding path.
Otherwise, the *json_val* is inserted if the json path addresses one of the following json values inside the *json_doc*:
- An inexistent object member of an existing json object. A (*key*, *value*) pair is added to the json object with the key deduced from the json path and the value being the *json_val*.
- An array index past of an existing json array's end. The array is filled with nulls after the initial end of the array and the *json_val* is inserted at the specified index.
The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid.
.. code-block:: sql
SELECT JSON_SET ('{"a":1}','$.a','b');
::
json_set('{"a":1}', '$.a', 'b')
======================
{"a":"b"}
.. code-block:: sql
SELECT JSON_SET ('{"a":1}','$.b','1');
::
json_set('{"a":1}', '$.b', '1')
======================
{"a":1,"b":"1"}
.. code-block:: sql
SELECT JSON_SET ('[0,1,2]','$[4]','1');
::
json_set('[0,1,2]', '$[4]', '1')
======================
[0,1,2,null,"1"]
.. _fn-json-replace:
JSON_REPLACE
===================================
.. function:: JSON_REPLACE (json_doc, json path, json_val [, json path, json_val] ...)
The **JSON_REPLACE** function returns a modified copy of the first argument. For each given <*json path*, *json_val*> pair, the function replaces the value only if another value is found at the corresponding path.
If the *json_path* does not exist inside the *json_doc*, the (*json path*, *json_val*) pair is ignored and has no effect.
The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid.
.. code-block:: sql
SELECT JSON_REPLACE ('{"a":1}','$.a','b');
::
json_replace('{"a":1}', '$.a', 'b')
======================
{"a":"b"}
No replacement is done if the *json path*` does not exist inside the *json_doc*.
.. code-block:: sql
SELECT JSON_REPLACE ('{"a":1}','$.b','1');
::
json_replace('{"a":1}', '$.b', '1')
======================
{"a":1}
.. code-block:: sql
SELECT JSON_REPLACE ('[0,1,2]','$[4]','1');
::
json_replace('[0,1,2]', '$[4]', '1')
======================
[0,1,2]
.. _fn-json-remove:
JSON_REMOVE
===================================
.. function:: JSON_REMOVE (json_doc, json path [, json path] ...)
The **JSON_REMOVE** function returns a modified copy of the first argument, by removing values from all given paths.
The json path arguments are evaluated one by one, from left to right. The result produced by evaluating a json path becomes the value against which the next json path is evaluated.
Returns **NULL** if any argument is **NULL**.
An error occurs if any argument is invalid or if a path points to the root or if a path does not exist.
.. code-block:: sql
SELECT JSON_REMOVE ('[0,1,2]','$[1]');
::
json_remove('[0,1,2]','$[1]')
======================
[0,2]
.. code-block:: sql
SELECT JSON_REMOVE ('{"a":1,"b":2}','$.a');
::
json_remove('{"a":1,"b":2}','$.a')
======================
{"b":2}
.. _fn-json-table:
JSON_TABLE
=====================
**JSON_TABLE** function facilitates transforming jsons into a table-like structures
that can be queried similarly as regular tables.
The transformation generates a single row or multiple rows, by expanding for
example the elements of a JSON_ARRAY.
The full syntax of **JSON_TABLE**:
::
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
::=
[, ] ...
::=
name FOR ORDINALITY
| name type PATH string_path
| name type EXISTS PATH string_path
| NESTED [PATH] string_path COLUMNS
::=
NULL | ERROR | DEFAULT value ON EMPTY
::=
NULL | ERROR | DEFAULT value ON ERROR
The *json_doc* expr must be an expression that results in a json_doc. This can be a constant json, a table's column or the result of a function or operator.
The *json path* must be a valid path and is used to extract json data to be evaluated in the **COLUMNS** clause.
The **COLUMNS** clause defines output column types and operations performed to get the output.
The [**AS**] *alias* clause is required.
**JSON_TABLE** supports four types of columns:
- *name* **FOR ORDINALITY**: this type keeps track of a row's number inside a **COLUMNS** clause. The column's type is **INTEGER**.
- *name* *type* **PATH** *json path* [**on empty**] [**on error**]: Columns of this type are used to extract json_values from the specified json paths. The extracted json data is then coerced to the specified type.
If the path does not exist, json value triggers the **on empty** clause. The **on error** clause is triggered if the extracted json value is not coercible to the target type.
- **on empty** determines the behavior of **JSON_TABLE** in case the path does not exist. **on empty** can have one of the following values:
- **NULL ON EMPTY**: the column is set to **NULL**. This is the default behavior.
- **ERROR ON EMPTY**: an error is thrown
- **DEFAULT** *value* **ON EMPTY**: *value* will be used instead of the missing value.
- **on error** can have one of the following values:
- **NULL ON ERROR**: the column is set to **NULL**. This is the default behavior.
- **ERROR ON ERROR**: an error is thrown.
- **DEFAULT** *value* **ON ERROR**: *value* will be used instead of the array/object/json scalar that failed coercion to desired column type.
- *name* *type* **EXISTS PATH** *json path*: this returns 1 if any data is present at the json path location, 0 otherwise.
- **NESTED** [**PATH**] *json path* **COLUMNS** (*column list*) generates from json data \
\found at path a separate subset of rows and columns that are combined \
\with the results of parent. Results are combined similarly as "for each" \
\ loops. The json path is relative to the parent's path. Same rules for \
\ **COLUMNS** clause are applied recursively.
.. code-block:: sql
SELECT * FROM JSON_TABLE (
'{"a":[1,[2,3]]}',
'$.a[*]' COLUMNS ( col INT PATH '$')
) AS jt;
::
col
======================
1 -- first value found at '$.a[*]' is 1 json scalar, which is coercible to 1
NULL -- second value found at '$.a[*]' is [2,3] json array which cannot be coerced to int, triggering NULL ON ERROR default behavior
Overriding the default on_error behavior, results in a different output from previous example:
.. code-block:: sql
SELECT * FROM JSON_TABLE (
'{"a":[1,[2,3]]}',
'$.a[*]' COLUMNS ( col INT PATH '$' DEFAULT '-1' ON ERROR)
) AS jt;
::
col
======================
1 -- first value found at '$.a[*]' is '1' json scalar, which is coercible to 1
-1 -- second value found at '$.a[*]' is '[2,3]' json array which cannot be coerced to int, triggering ON ERROR
**ON EMPTY** example:
.. code-block:: sql
SELECT * FROM JSON_TABLE (
'{"a":1}',
'$' COLUMNS ( col1 INT PATH '$.a',
col2 INT PATH '$.b',
col3 INT PATH '$.c' DEFAULT '0' ON EMPTY)
) AS jt;
::
col1 col2 col3
=======================================
1 NULL 0
In the example below, '$.*' path will be used to make the parent columns receive root json object's member values one by one. Column a shows what is processed. Each member's value of
the root object will then be processed further by the **NESTED** [**PATH**] clause. **NESTED PATH** uses path '$[*]' take each element of the array to be further processed by its columns.
**FOR ORDINALITY** columns track the count of the current processed element. In the example's result we can see that for each new element in a column, the *ord* column's value also gets incremented.
**FOR ORDINALITY** *nested_ord* column also acts as a counter of the number of elements processed by sibling columns. The nested **FOR ORDINALITY** column gets reset after finishing each processing batch.
The third member's value, 6 cannot be treated as an array and therefore cannot be processed by the nested columns. Nested columns will yield **NULL** values.
.. code-block:: sql
SELECT * FROM JSON_TABLE (
'{"a":[1,2],"b":[3,4,5],"d":6,"c":[7]}',
'$.*' COLUMNS ( ord FOR ORDINALITY,
col JSON PATH '$',
NESTED PATH '$[*]' COLUMNS ( nested_ord FOR ORDINALITY,
nested_col JSON PATH '$'))
) AS jt;
::
ord col nested_ord nested_col
=====================================================================
1 [1,2] 1 1
1 [1,2] 2 2
2 [3,4,5] 1 3
2 [3,4,5] 2 4
2 [3,4,5] 3 5
3 6 NULL NULL
4 [7] 1 7
The following example showcases how multiple same-level **NESTED** [**PATH**] clauses are treated by the **JSON_TABLE**. The value to be processed gets passed once, one by one and in order, to each of the **NESTED** [**PATH**] clauses.
During processing of a value by a **NESTED** [**PATH**] clause, any sibling **NESTED** [**PATH**] clauses will fill their column with **NULL** values.
.. code-block:: sql
SELECT * FROM JSON_TABLE (
'{"a":{"key1":[1,2], "key2":[3,4,5]},"b":{"key1":6, "key2":[7]}}',
'$.*' COLUMNS ( ord FOR ORDINALITY,
col JSON PATH '$',
NESTED PATH '$.key1[*]' COLUMNS ( nested_ord1 FOR ORDINALITY,
nested_col1 JSON PATH '$'),
NESTED PATH '$.key2[*]' COLUMNS ( nested_ord2 FOR ORDINALITY,
nested_col2 JSON PATH '$'))
) AS jt;
::
ord col nested_ord1 nested_col1 nested_ord2 nested_col2
===================================================================================================================
1 {"key1":[1,2],"key2":[3,4,5]} 1 1 NULL NULL
1 {"key1":[1,2],"key2":[3,4,5]} 2 2 NULL NULL
1 {"key1":[1,2],"key2":[3,4,5]} NULL NULL 1 3
1 {"key1":[1,2],"key2":[3,4,5]} NULL NULL 2 4
1 {"key1":[1,2],"key2":[3,4,5]} NULL NULL 3 5
2 {"key1":6,"key2":[7]} NULL NULL 1 7
An example for multiple layers **NESTED** [**PATH**] clauses:
.. code-block:: sql
SELECT * FROM JSON_TABLE (
'{"a":{"key1":[1,2], "key2":[3,4,5]},"b":{"key1":6, "key2":[7]}}',
'$.*' COLUMNS ( ord FOR ORDINALITY,
col JSON PATH '$',
NESTED PATH '$.*' COLUMNS ( nested_ord1 FOR ORDINALITY,
nested_col1 JSON PATH '$',
NESTED PATH '$[*]' COLUMNS ( nested_ord11 FOR ORDINALITY,
nested_col11 JSON PATH '$')),
NESTED PATH '$.key2[*]' COLUMNS ( nested_ord2 FOR ORDINALITY,
nested_col2 JSON PATH '$'))
) AS jt;
::
ord col nested_ord1 nested_col1 nested_ord11 nested_col11 nested_ord2 nested_col2
=======================================================================================================================================================
1 {"key1":[1,2],"key2":[3,4,5]} 1 [1,2] 1 1 NULL NULL
1 {"key1":[1,2],"key2":[3,4,5]} 1 [1,2] 2 2 NULL NULL
1 {"key1":[1,2],"key2":[3,4,5]} 2 [3,4,5] 1 3 NULL NULL
1 {"key1":[1,2],"key2":[3,4,5]} 2 [3,4,5] 2 4 NULL NULL
1 {"key1":[1,2],"key2":[3,4,5]} 2 [3,4,5] 3 5 NULL NULL
1 {"key1":[1,2],"key2":[3,4,5]} NULL NULL NULL NULL 1 3
1 {"key1":[1,2],"key2":[3,4,5]} NULL NULL NULL NULL 2 4
1 {"key1":[1,2],"key2":[3,4,5]} NULL NULL NULL NULL 3 5
2 {"key1":6,"key2":[7]} 1 6 NULL NULL NULL NULL
2 {"key1":6,"key2":[7]} 2 [7] 1 7 NULL NULL
2 {"key1":6,"key2":[7]} NULL NULL NULL NULL 1 7
.. _fn-json-adjacency-list:
JSON_ADJACENCY_LIST
===================================
.. function:: JSON_ADJACENCY_LIST(vertex)
The **JSON_ADJACENCY_LIST** function returns a JSON representation of the adjacency list for a given vertex in a graph query. The adjacency list shows all edges and connected vertices from the specified vertex, providing a view of the vertex's neighborhood in the graph.
:param vertex: A vertex reference from a graph query
:rtype: JSON
Returns **NULL** if the argument is **NULL**.
**Setup Example Schema**
.. code-block:: sql
CREATE VERTEX TABLE person (name STRING);
CREATE VERTEX TABLE product (type STRING);
INSERT VERTEX INTO Person VALUES ('John Doe');
INSERT VERTEX INTO Person VALUES ('Lily Bush');
INSERT VERTEX INTO Person VALUES ('Josh William');
INSERT VERTEX INTO Person VALUES ('Mary Clark');
CREATE EDGE TABLE ordered;
INSERT VERTEX INTO Product VALUES ('Beer');
INSERT VERTEX INTO Product VALUES ('Diaper');
INSERT VERTEX INTO Product VALUES ('Toy Car');
INSERT VERTEX INTO Product VALUES ('Toy Train');
INSERT EDGE FROM (SELECT Person FROM Person WHERE name = 'John Doe') TO (SELECT Product FROM Product WHERE type = 'Beer') INTO Ordered VALUES ();
INSERT EDGE FROM (SELECT Person FROM Person WHERE name = 'Lily Bush') TO (SELECT Product FROM Product WHERE type = 'Diaper') INTO Ordered VALUES ();
INSERT EDGE FROM (SELECT Person FROM Person WHERE name = 'Josh William') TO (SELECT Product FROM Product WHERE type = 'Toy Car') INTO Ordered VALUES ();
INSERT EDGE FROM (SELECT Person FROM Person WHERE name = 'Mary Clark') TO (SELECT Product FROM Product WHERE type = 'Toy Train') INTO Ordered VALUES ();
**Example 1: Get adjacency list for a specific vertex**
.. code-block:: sql
SELECT a.name, JSON_PRETTY(JSON_ADJACENCY_LIST(a))
FROM MATCH (a:Person)->(b:Product)
WHERE a.name = 'Lily Bush'
ORDER BY 1,2;
This retrieves the adjacency list for the vertex representing 'Lily Bush', showing all connected vertices and edges.
**Example 2: Get adjacency list with path constraints**
.. code-block:: sql
SELECT a.name, JSON_PRETTY(JSON_ADJACENCY_LIST(a))
FROM MATCH (a:Person)-[o:ordered{1,2}]->(b:Product)
ORDER BY 1,2;
This retrieves adjacency lists for vertices with path length constraints, showing connections within the specified path range.
.. _fn-json-edge-properties:
JSON_EDGE_PROPERTIES
===================================
.. function:: JSON_EDGE_PROPERTIES(edge)
The **JSON_EDGE_PROPERTIES** function returns a JSON object containing all properties of an edge from a graph query. This function is used with edges in MATCH clauses to extract their properties as JSON.
:param edge: An edge reference from a graph query
:rtype: JSON
Returns **NULL** if the argument is **NULL**.
**Setup Example Schema**
.. code-block:: sql
CREATE VERTEX TABLE person (name STRING, id STRING);
CREATE EDGE TABLE knows (id STRING);
CREATE EDGE TABLE is_friends_with (id STRING);
INSERT VERTEX INTO Person VALUES ('Alex', '0');
INSERT VERTEX INTO Person VALUES ('Brandon', '1');
INSERT VERTEX INTO Person VALUES ('Chloe', '2');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '1') INTO knows VALUES ('0');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '2') INTO knows VALUES ('1');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '1') TO (SELECT Person FROM Person WHERE id = '2') INTO knows VALUES ('2');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '1') TO (SELECT Person FROM Person WHERE id = '2') INTO is_friends_with VALUES ('5');
**Example 1: Extract properties from edges**
.. code-block:: sql
SELECT JSON_EDGE_PROPERTIES(e)
FROM MATCH (p1:person)-[e:knows]->(p2:person)
ORDER BY 1;
This retrieves JSON representation of properties for all edges of type 'knows'.
**Example 2: Filter and extract edge properties**
.. code-block:: sql
SELECT JSON_EDGE_PROPERTIES(e)
FROM MATCH (p1:person)-[e:is_friends_with]->(p2:person)
WHERE p1.name like '%don'
ORDER BY 1;
This retrieves edge properties filtered by source vertex name.
.. _fn-json-graph:
JSON_GRAPH
===================================
.. function:: JSON_GRAPH(graph_element [, graph_element, ...])
The **JSON_GRAPH** function returns a JSON representation of a graph structure containing one or more graph elements (vertices or edges). It can accept multiple graph elements as arguments and returns a unified JSON structure representing the graph formed by these elements.
:param graph_element: One or more vertex or edge references from a graph query
:rtype: JSON
Returns **NULL** if all arguments are **NULL**.
**Setup Example Schema**
The following examples use the same schema as JSON_INFO:
.. code-block:: sql
CREATE VERTEX TABLE person (name STRING, id STRING);
CREATE EDGE TABLE knows (id STRING);
INSERT VERTEX INTO Person VALUES ('Alex', '0');
INSERT VERTEX INTO Person VALUES ('Brandon', '1');
INSERT VERTEX INTO Person VALUES ('Chloe', '2');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '1') INTO knows VALUES ('0');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '2') INTO knows VALUES ('1');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '1') TO (SELECT Person FROM Person WHERE id = '2') INTO knows VALUES ('2');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '2') TO (SELECT Person FROM Person WHERE id = '1') INTO knows VALUES ('3');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '0') INTO knows VALUES ('4');
**Example 1: Get graph for multiple vertices**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_GRAPH(p1, p2))
FROM MATCH (p1:person)-[k:knows]->(p2:person)
WHERE p1.id = '0' AND p2.id = '1';
This retrieves a JSON graph representation containing both source and destination vertices.
**Example 2: Get graph for a single vertex**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_GRAPH(p1))
FROM MATCH (p1:person)-[k:knows]->(p2:person)
WHERE p1.id = '1' AND p2.id = '2';
This retrieves a JSON graph representation for a single vertex in the pattern.
**Example 3: Get graph for edges**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_GRAPH(k))
FROM MATCH (p1:person)-[k:knows]->(p2:person)
WHERE k.id = '2';
This retrieves a JSON graph representation for a specific edge.
**Example 4: Get graph for multiple edges**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_GRAPH(k1, k2))
FROM MATCH (p1:person)-[k1:knows]->(p2:person), (p2:person)-[k2:knows]->(p3:person)
WHERE k1.id = '0' AND k2.id = '1';
This retrieves a JSON graph representation containing multiple edges forming a path.
**Example 5: Get graph with path constraints**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_GRAPH(p1))
FROM MATCH (p1:person)-[{1,2}]->(p2:person)
WHERE p2.name IN ('Alex', 'Brandon');
This retrieves graph representations for vertices matching path length constraints.
.. _fn-json-graph-heap-memory-usage:
JSON_GRAPH_HEAP_MEMORY_USAGE
===================================
.. function:: JSON_GRAPH_HEAP_MEMORY_USAGE()
The **JSON_GRAPH_HEAP_MEMORY_USAGE** function returns a JSON object containing information about the heap memory usage of graph-related data structures in the database. This function provides insights into memory consumption for graph operations and can be useful for monitoring and optimization purposes.
:rtype: JSON
Returns a JSON object with memory usage statistics.
**Example: Get graph heap memory usage**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_GRAPH_HEAP_MEMORY_USAGE());
This query retrieves the current heap memory usage information for graph structures in JSON format, providing details about memory allocation and consumption for graph-related operations in the database.
.. _fn-json-info:
JSON_INFO
===================================
.. function:: JSON_INFO(graph_element)
The **JSON_INFO** function returns a JSON object containing comprehensive information about a graph element (vertex or edge) from a graph query. This includes both the properties and metadata about the element, providing a complete view of the graph element.
:param graph_element: A vertex or edge reference from a graph query
:rtype: JSON
Returns **NULL** if the argument is **NULL**.
**Setup Example Schema**
.. code-block:: sql
CREATE VERTEX TABLE person (name STRING, id STRING);
CREATE EDGE TABLE loves (id STRING);
CREATE EDGE TABLE hates (id STRING);
INSERT VERTEX INTO Person VALUES ('John', '0');
INSERT VERTEX INTO Person VALUES ('Anna', '1');
INSERT VERTEX INTO Person VALUES ('Alex', '2');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '1') INTO Loves VALUES ('0');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '2') INTO Loves VALUES ('1');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '1') TO (SELECT Person FROM Person WHERE id = '2') INTO Loves VALUES ('2');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '2') TO (SELECT Person FROM Person WHERE id = '1') INTO Loves VALUES ('3');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '0') TO (SELECT Person FROM Person WHERE id = '0') INTO Loves VALUES ('4');
INSERT EDGE FROM (SELECT Person FROM Person WHERE id = '1') TO (SELECT Person FROM Person WHERE id = '2') INTO Hates VALUES ('5');
**Example 1: Get information for vertices**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_INFO(p))
FROM MATCH (p:person)
ORDER BY 1;
This retrieves comprehensive JSON information for all vertices in the person table.
**Example 2: Get information in simple patterns**
.. code-block:: sql
SELECT JSON_PRETTY(JSON_INFO(p1))
FROM MATCH (p1:person)->(p2:person)
ORDER BY 1;
This retrieves information for source vertices in simple directed relationships.
**Example 3: Get information for edges**
.. code-block:: sql
SELECT JSON_INFO(l)
FROM MATCH (p1:person)-[l:knows]->(p2:person)
WHERE p1.id = '0' AND p2.id = '0'
ORDER BY 1;
This retrieves JSON information for edges matching specific vertex conditions.
**Example 4: Use with multiple edges**
.. code-block:: sql
SELECT JSON_INFO(l), JSON_INFO(h)
FROM MATCH (p1:person)-[h:knows]->(p2:person), (p2:person)-[l:knows]->(p3:person)
ORDER BY 1,2;
This retrieves information for multiple edges with path length constraints in complex graph patterns.
.. _fn-json-node-properties:
JSON_NODE_PROPERTIES
===================================
.. function:: JSON_NODE_PROPERTIES(vertex)
The **JSON_NODE_PROPERTIES** function returns a JSON object containing all properties of a vertex (node) from a graph query. This function is used with vertices in MATCH clauses to extract their properties as JSON.
:param vertex: A vertex reference from a graph query
:rtype: JSON
Returns **NULL** if the argument is **NULL**.
**Example 1: Extract properties from vertices**
.. code-block:: sql
SELECT JSON_NODE_PROPERTIES(p)
FROM MATCH (p:person)
ORDER BY 1;
This retrieves JSON representation of properties for all vertices in the person table.
**Example 2: Use in complex patterns**
.. code-block:: sql
SELECT p1.name, k.id, p2.name, JSON_NODE_PROPERTIES(p1)
FROM MATCH (p1:person)-[k:knows]->(p2:person)
ORDER BY 1,2,3,4;
This retrieves node properties along with other query results.
.. _fn-json-properties:
JSON_PROPERTIES
===================================
.. function:: JSON_PROPERTIES(graph_element)
The **JSON_PROPERTIES** function returns a JSON object containing all properties of a graph element (vertex or edge) from a graph query. This is a general function that works with both vertices and edges in MATCH clauses.
:param graph_element: A vertex or edge reference from a graph query
:rtype: JSON
Returns **NULL** if the argument is **NULL**.
**Example 1: Extract properties from vertices**
.. code-block:: sql
SELECT JSON_PROPERTIES(p)
FROM MATCH (p:person)
ORDER BY 1;
This retrieves JSON representation of properties for all vertices in the person table.
**Example 2: Extract properties in complex patterns**
.. code-block:: sql
SELECT JSON_PROPERTIES(p1), JSON_PROPERTIES(p2)
FROM MATCH (p1:person)->(p2:person), (p2:person)->(p1:person)
WHERE p2.name='Brandon'
ORDER BY 1,2;
This retrieves properties from multiple vertices in a bidirectional relationship pattern.
**Example 3: Extract edge properties**
.. code-block:: sql
SELECT p1.name, e1.id, p2.name, JSON_PROPERTIES(e1)
FROM MATCH (p1:person)-[e1:knows]->(p2:person)
ORDER BY 1,2,3,4;
This retrieves edge properties along with vertex properties.
.. _fn-json-topology-record:
JSON_TOPOLOGY_RECORD
===================================
.. function:: JSON_TOPOLOGY_RECORD (vertex_or_topology_record)
The **JSON_TOPOLOGY_RECORD** function returns a json representation of the topology information for a given vertex or topology record.
This function is useful for examining the graph structure and relationships of vertices in the database.
Returns **NULL** if the argument is **NULL**.
The following examples demonstrate the usage of **JSON_TOPOLOGY_RECORD** with vertices and edges.
First, create the vertex and edge tables:
.. code-block:: sql
CREATE VERTEX TABLE vt (id int);
CREATE VERTEX TABLE vt2 (id int);
CREATE EDGE TABLE et (id int);
CREATE EDGE TABLE et2 (id int);
Insert sample data into the vertex tables:
.. code-block:: sql
INSERT INTO vt VALUES (1);
INSERT INTO vt VALUES (2);
INSERT INTO vt VALUES (3);
INSERT INTO vt2 VALUES (4);
INSERT INTO vt2 VALUES (5);
INSERT INTO vt2 VALUES (6);
Create directed edges between vertices:
.. code-block:: sql
INSERT DIRECTED EDGE FROM (SELECT vt FROM vt WHERE id=1) TO (SELECT vt FROM vt WHERE id=2) INTO et VALUES (1);
INSERT DIRECTED EDGE FROM (SELECT vt FROM vt WHERE id=2) TO (SELECT vt FROM vt WHERE id=3) INTO et VALUES (2);
INSERT DIRECTED EDGE FROM (SELECT vt FROM vt WHERE id=3) TO (SELECT vt2 FROM vt2 WHERE id=4) INTO et VALUES (3);
INSERT DIRECTED EDGE FROM (SELECT vt FROM vt WHERE id=3) TO (SELECT vt2 FROM vt2 WHERE id=4) INTO et2 VALUES (4);
Now you can use **JSON_TOPOLOGY_RECORD** to retrieve the topology information:
.. code-block:: sql
SELECT JSON_PRETTY(JSON_TOPOLOGY_RECORD((SELECT vt FROM vt LIMIT 1))) FROM dual;
This returns the topology record for the first vertex in table vt.
.. code-block:: sql
SELECT JSON_PRETTY(JSON_TOPOLOGY_RECORD((SELECT vt FROM vt WHERE id = 3))) FROM dual;
This returns the topology record for the vertex with id=3, showing its connections.
.. code-block:: sql
SELECT JSON_PRETTY(JSON_TOPOLOGY_RECORD((SELECT vt FROM vt WHERE id = 2))) FROM dual;
This returns the topology record for the vertex with id=2.
**JSON_TOPOLOGY_RECORD** can also be used within **MATCH** clauses to analyze graph patterns:
.. code-block:: sql
SELECT JSON_PRETTY(JSON_TOPOLOGY_RECORD(a)) FROM MATCH (a:vt) ORDER BY 1;
This retrieves the topology records for all vertices in the vt table.
.. code-block:: sql
SELECT JSON_PRETTY(JSON_TOPOLOGY_RECORD(a)) FROM MATCH (a:vt)-[e:et2]->(b:vt2) ORDER BY 1;
This retrieves the topology record for vertices that have edges of type et2 connecting to vt2 vertices.
You can also query the system topology table directly:
.. code-block:: sql
SELECT JSON_PRETTY(JSON_TOPOLOGY_RECORD((SELECT _db_graph_topology FROM _db_graph_topology LIMIT 1))) FROM dual;
This retrieves topology information from the system's internal graph topology table.