Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Manipulate JSON documents natively. Use these functions to extract values, modify structures, and query data stored in JSON format directly within your SQL statements.
Available from version 11.2, this function compares two JSON arrays and returns a new array containing only the items present in both.
SET @json1= '[1,2,3]';
SET @json2= '[1,2,4]';
SELECT json_array_intersect(@json1, @json2);
+--------------------------------------+
| json_array_intersect(@json1, @json2) |
+--------------------------------------+
| [1, 2] |
+--------------------------------------+Create a JSON array. This function evaluates a list of values and returns a JSON array containing those values.
SELECT Json_Array(56, 3.1416, 'My name is "Foo"', NULL);
+--------------------------------------------------+
| Json_Array(56, 3.1416, 'My name is "Foo"', NULL) |
+--------------------------------------------------+
| [56, 3.1416, "My name is \"Foo\"", null] |
+--------------------------------------------------+This function inserts values into a JSON document at a specified path, returning the modified document, and supports evaluating multiple path-value pairs sequentially.
JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)Inserts a value into a JSON document, returning the modified document, or NULL if any of the arguments are NULL.
Evaluation is performed from left to right, with the resulting document from the previous pair becoming the new value against which the next pair is evaluated.
If the json_doc is not a valid JSON document, or if any of the paths are not valid, or contain a * or ** wildcard, an error is returned.
This page is licensed: CC BY-SA / Gnu FDL
Learn the difference between JSON_QUERY and JSON_VALUE in MariaDB. JSON_QUERY returns objects or arrays, while JSON_VALUE extracts scalar values.
The primary difference between the two functions is that JSON_QUERY returns an object or an array, while JSON_VALUE returns a scalar.
Take the following JSON document as an example:
SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Monty" }';Note that data member "x" is an array, and data members "y" and "z" are strings. The following examples demonstrate the differences between the two functions.
SELECT JSON_QUERY(@json,'$'), JSON_VALUE(@json,'$');
+--------------------------------------------+-----------------------+
| JSON_QUERY(@json,'$') | JSON_VALUE(@json,'$') |
+--------------------------------------------+-----------------------+
| { "x": [0,1], "y": "[0,1]", "z": "Monty" } | NULL |
+--------------------------------------------+-----------------------+
SELECT JSON_QUERY(@json,'$.x'), JSON_VALUE(@json,'$.x');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.x') | JSON_VALUE(@json,'$.x') |
+-------------------------+-------------------------+
| [0,1] | NULL |
+-------------------------+-------------------------+
SELECT JSON_QUERY(@json,'$.y'), JSON_VALUE(@json,'$.y');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.y') | JSON_VALUE(@json,'$.y') |
+-------------------------+-------------------------+
| NULL | [0,1] |
+-------------------------+-------------------------+
SELECT JSON_QUERY(@json,'$.z'), JSON_VALUE(@json,'$.z');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.z') | JSON_VALUE(@json,'$.z') |
+-------------------------+-------------------------+
| NULL | Monty |
+-------------------------+-------------------------+
SELECT JSON_QUERY(@json,'$.x[0]'), JSON_VALUE(@json,'$.x[0]');
+----------------------------+----------------------------+
| JSON_QUERY(@json,'$.x[0]') | JSON_VALUE(@json,'$.x[0]') |
+----------------------------+----------------------------+
| NULL | 0 |
+----------------------------+----------------------------+This page is licensed: CC BY-SA / Gnu FDL
Return the length of a JSON document. This function returns the length of a JSON document or, if a path is given, the length of the value within the path.
JSON_LENGTH(json_doc[, path])Returns the length of a JSON document, or, if the optional path argument is given, the length of the value within the document specified by the path.
Returns NULL if any of the arguments argument are null or the path argument does not identify a value in the document.
An error occurs if the JSON document is invalid, the path is invalid or if the path contains a * or ** wildcard.
Length will be determined as follow:
A scalar's length is always 1.
If an array, the number of elements in the array.
If an object, the number of members in the object.
The length of nested arrays or objects are not counted.
This page is licensed: CC BY-SA / Gnu FDL
Learn about JSON_PRETTY in MariaDB. This function is an alias for JSON_DETAILED, serving to format JSON documents in a human-readable way by adding indentation and newlines.
JSON_PRETTY is an alias for JSON_DETAILED.
This page is licensed: CC BY-SA / Gnu FDL
Discover JSON_LOOSE in MariaDB. This function adds spaces to a JSON document to improve its readability, providing a format that is easier for humans to scan than compact JSON.
Return keys from a JSON object. This function returns the keys from the top-level value of a JSON object as a JSON array.
Explore JSON_MERGE_PRESERVE in MariaDB. This function merges JSON documents while keeping all members, including those with duplicate keys, effectively acting as a synonym for the legacy JSON_MERGE.
SET @json = '[1, 2, [3, 4]]';
SELECT JSON_ARRAY_INSERT(@json, '$[0]', 5);
+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[0]', 5) |
+-------------------------------------+
| [5, 1, 2, [3, 4]] |
+-------------------------------------+
SELECT JSON_ARRAY_INSERT(@json, '$[1]', 6);
+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[1]', 6) |
+-------------------------------------+
| [1, 6, 2, [3, 4]] |
+-------------------------------------+
SELECT JSON_ARRAY_INSERT(@json, '$[1]', 6, '$[2]', 7);
+------------------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[1]', 6, '$[2]', 7) |
+------------------------------------------------+
| [1, 6, 7, 2, [3, 4]] |
+------------------------------------------------+SET @j = '{ "A":1,"B":[2,3]}';
SELECT JSON_LOOSE(@j), @j;
+-----------------------+--------------------+
| JSON_LOOSE(@j) | @j |
+-----------------------+--------------------+
| {"A": 1, "B": [2, 3]} | { "A":1,"B":[2,3]} |
+-----------------------+--------------------+JSON_ARRAYAGG returns a JSON array containing an element for each value in a given set of JSON or SQL values. It acts on a column or an expression that evaluates to a single value.
The maximum returned length in bytes is determined by the group_concat_max_len server system variable.
Returns NULL in the case of an error, or if the result contains no rows.
JSON_ARRAYAGG cannot currently be used as a window function.
The full syntax is as follows:
This page is licensed: CC BY-SA / Gnu FDL
JSON_ARRAYAGG(column_or_expression)Scalar values or empty arrays or objects have a depth of 1.
Arrays with only scalar values and objects with only scalar values for all keys have depth of 1.
In all other cases, the depth can be 2 or greater.
There is no maximum depth level — it's unlimited.
For more information, see this blog post.
The maximum depth is 32.
This page is licensed: CC BY-SA / Gnu FDL
0NULLThe return_arg can be one or all:
one - Returns 1 if at least one path exists within the JSON document.
all - Returns 1 only if all paths exist within the JSON document.
This page is licensed: CC BY-SA / Gnu FDL
JSON_CONTAINS_PATH(json_doc, return_arg, path[, path] ...)JSON_PRETTY is an alias for JSON_DETAILED .
JSON_PRETTY is not available as an alias for JSON_DETAILED .
JSON video tutorial covering JSON_DETAILED.
This page is licensed: CC BY-SA / Gnu FDL
JSON video tutorial covering JSON_COMPACT.
This page is licensed: CC BY-SA / Gnu FDL
JSON_COMPACT(json_doc)JSON_OVERLAPS() compares two json documents and returns true if they have at least one common
key-value pair between two objects, array element common between two arrays, or array element common with scalar if one of the arguments is a scalar and other is an array. If two json documents are scalars, it returns true if they have same type and value.
If none of the above conditions are satisfied then it returns false.
Partial match is considered as no-match.
This page is licensed: CC BY-SA / Gnu FDL
JSON_OVERLAPS(json_doc1, json_doc2)Recursively sorts keys and removes spaces, allowing comparison of json documents for equality.
We may wish our application to use the database to enforce a unique constraint on the JSON contents, and we can do so using the JSON_NORMALIZE function in combination with a unique key.
For example, if we have a table with a JSON column:
Add a unique constraint using JSON_NORMALIZE like this:
We can test this by first inserting a row as normal:
And then seeing what happens with a different string which would produce the same JSON object:
This page is licensed: CC BY-SA / Gnu FDL
Returns NULL if any of the arguments are null, a given path does not locate an object, or if the json_doc argument is not an object.
An error will occur if JSON document is invalid, the path is invalid or if the path contains a * or ** wildcard.
This page is licensed: CC BY-SA / Gnu FDL
JSON_KEYS(json_doc[, path])NULLJSON_MERGE_PRESERVE is a synonym for JSON_MERGE, which has been deprecated.
Unlike JSON_MERGE_PATCH, members with duplicate keys are preserved.
This page is licensed: CC BY-SA / Gnu FDL
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)It is used to convert all JSON objects found in a JSON document to JSON arrays where each item in the outer array represents a single key-value pair from the object. It is used when we want not just common keys, but also common values. It can be used in conjunction with JSON_ARRAY_INTERSECT().
This page is licensed: CC BY-SA / Gnu FDL
JSON_OBJECT_TO_ARRAY(Obj)This page is licensed: CC BY-SA / Gnu FDL
JSON_QUERY(json_doc, path)SELECT json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1');
+-----------------------------------------------------+
| json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1') |
+-----------------------------------------------------+
| {"a":1, "b":[1,2]} |
+-----------------------------------------------------+
SELECT json_query('{"key1":123, "key1": [1,2,3]}', '$.key1');
+-------------------------------------------------------+
| json_query('{"key1":123, "key1": [1,2,3]}', '$.key1') |
+-------------------------------------------------------+
| [1,2,3] |
+-------------------------------------------------------+JSON_ARRAYAGG([DISTINCT] expr
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}])CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 1),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(2, 2);
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
+-------------------+-------------------+
| JSON_ARRAYAGG(a) | JSON_ARRAYAGG(b) |
+-------------------+-------------------+
| [1,2,1,2,3,2,2,2] | [1,1,1,1,2,2,2,2] |
+-------------------+-------------------+
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
+------------------+------------------+
| JSON_ARRAYAGG(a) | JSON_ARRAYAGG(b) |
+------------------+------------------+
| [1,2,1,2] | [1,1,1,1] |
| [3,2,2,2] | [2,2,2,2] |
+------------------+------------------+JSON_DEPTH(json_doc)SELECT JSON_DEPTH('[]'), JSON_DEPTH('true'), JSON_DEPTH('{}');
+------------------+--------------------+------------------+
| JSON_DEPTH('[]') | JSON_DEPTH('true') | JSON_DEPTH('{}') |
+------------------+--------------------+------------------+
| 1 | 1 | 1 |
+------------------+--------------------+------------------+
SELECT JSON_DEPTH('[1, 2, 3]'), JSON_DEPTH('[[], {}, []]');
+-------------------------+----------------------------+
| JSON_DEPTH('[1, 2, 3]') | JSON_DEPTH('[[], {}, []]') |
+-------------------------+----------------------------+
| 2 | 2 |
+-------------------------+----------------------------+
SELECT JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]');
+---------------------------------------+
| JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]') |
+---------------------------------------+
| 3 |
+---------------------------------------+SET @json = '{"A": 1, "B": [2], "C": [3, 4]}';
SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A', '$.D');
+------------------------------------------------+
| JSON_CONTAINS_PATH(@json, 'one', '$.A', '$.D') |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set (0.00 sec)
SELECT JSON_CONTAINS_PATH(@json, 'all', '$.A', '$.D');
+------------------------------------------------+
| JSON_CONTAINS_PATH(@json, 'all', '$.A', '$.D') |
+------------------------------------------------+
| 0 |
+------------------------------------------------+JSON_DETAILED(json_doc[, tab_size])
JSON_PRETTY(json_doc[, tab_size])SET @j = '{ "A":1,"B":[2,3]}';
SELECT @j;
+--------------------+
| @j |
+--------------------+
| { "A":1,"B":[2,3]} |
+--------------------+
SELECT JSON_DETAILED(@j);
+------------------------------------------------------------+
| JSON_DETAILED(@j) |
+------------------------------------------------------------+
| {
"A": 1,
"B":
[
2,
3
]
} |
+------------------------------------------------------------+SET @j = '{ "A": 1, "B": [2, 3]}';
SELECT JSON_COMPACT(@j), @j;
+-------------------+------------------------+
| JSON_COMPACT(@j) | @j |
+-------------------+------------------------+
| {"A":1,"B":[2,3]} | { "A": 1, "B": [2, 3]} |
+-------------------+------------------------+SELECT JSON_OVERLAPS('false', 'false');
+---------------------------------+
| JSON_OVERLAPS('false', 'false') |
+---------------------------------+
| 1 |
+---------------------------------+
SELECT JSON_OVERLAPS('true', '["abc", 1, 2, true, false]');
+----------------------------------------------------+
| JSON_OVERLAPS('true','["abc", 1, 2, true, false]') |
+----------------------------------------------------+
| 1 |
+----------------------------------------------------+
SELECT JSON_OVERLAPS('{"A": 1, "B": {"C":2}}', '{"A": 2, "B": {"C":2}}') AS is_overlap;
+---------------------+
| is_overlap |
+---------------------+
| 1 |
+---------------------+SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, [1]]') AS is_overlap;
+--------------------- +
| is_overlap |
+----------------------+
| 0 |
+----------------------+JSON_NORMALIZE(json)CREATE TABLE t1 (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
val JSON,
/* other columns here */
PRIMARY KEY (id)
);ALTER TABLE t1
ADD COLUMN jnorm JSON AS (JSON_NORMALIZE(val)) VIRTUAL,
ADD UNIQUE KEY (jnorm);INSERT INTO t1 (val) VALUES ('{"name":"alice","color":"blue"}');INSERT INTO t1 (val) VALUES ('{ "color": "blue", "name": "alice" }');
ERROR 1062 (23000): Duplicate entry '{"color":"blue","name":"alice"}' for key 'jnorm'SELECT JSON_KEYS('{"A": 1, "B": {"C": 2}}');
+--------------------------------------+
| JSON_KEYS('{"A": 1, "B": {"C": 2}}') |
+--------------------------------------+
| ["A", "B"] |
+--------------------------------------+
SELECT JSON_KEYS('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C');
+-----------------------------------------------------+
| JSON_KEYS('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C') |
+-----------------------------------------------------+
| ["D"] |
+-----------------------------------------------------+SET @json1 = '[1, 2]';
SET @json2 = '[2, 3]';
SELECT JSON_MERGE_PATCH(@json1,@json2),JSON_MERGE_PRESERVE(@json1,@json2);
+---------------------------------+------------------------------------+
| JSON_MERGE_PATCH(@json1,@json2) | JSON_MERGE_PRESERVE(@json1,@json2) |
+---------------------------------+------------------------------------+
| [2, 3] | [1, 2, 2, 3] |
+---------------------------------+------------------------------------+SET @obj1= '{ "a": [1, 2, 3], "b": { "key1":"val1", "key2": {"key3":"val3"} }}';
SELECT JSON_OBJECT_TO_ARRAY(@obj1);
+-----------------------------------------------------------------------+
| JSON_OBJECT_TO_ARRAY(@obj1) |
+-----------------------------------------------------------------------+
| [["a", [1, 2, 3]], ["b", {"key1": "val1", "key2": {"key3": "val3"}}]] |
+-----------------------------------------------------------------------+Understand JSONPath syntax. This guide explains how to use JSONPath to select and extract specific elements, objects, or arrays from JSON documents.
A number of JSON functions accept JSON Path expressions. MariaDB defines this path as follows:
The path starts with an optional path mode. At the moment, MariaDB supports only the "lax" mode, which is also the mode that is used when it is not explicitly specified.
The $ symbol represents the context item. The search always starts from the context item; because of that, the path always starts with $.
Then, it is followed by zero or more steps, which select element(s) in the JSON document. A step may be one of the following:
Object member selector.
Array element selector.
Wildcard selector.
To select member(s) in a JSON object, one can use one of the following:
.memberName selects the value of the member with name memberName.
."memberName" - the same as above but allows one to select a member with a name that's not a valid identifier (that is, has space, dot, and/or other characters).
.* - selects the values of all members of the object.
If the current item is an array (instead of an object), nothing will be selected.
To select elements of an array, one can use one of the following:
[N] selects element number N in the array. The elements are counted from zero.
[*] selects all elements in the array.
If the current item is an object (instead of an array), nothing will be selected.
JSON path supports negative indexes in an array, 'last' keyword and range notation ('to' keyword) for accessing array elements. Negative indexes start from -1.
[-N] selects n th element from end.
[last-N] selects n th element from the last element.
[M to N] selects range of elements starting from index M to N.
This produces output for first index of eighth from last element of a two dimensional array.
Note: In range notation, when M > N ( when M,N are greater than or equal to 0) or (size of array - M or size of array - N when M, N are less than 0), then it is treated as an impossible range and NULL is returned.
The wildcard step, **, recursively selects all child elements of the current element. Both array elements and object members are selected.
The wildcard step must not be the last step in the JSONPath expression. It must be followed by an array or object member selector step.
For example, this query selects all object members named price in the document:
This query, however, selects the second element in each of the arrays present in the document:
MariaDB's JSONPath syntax supports a subset of JSON Path's definition in the SQL Standard. The most notable things not supported are the strict mode and filters.
MariaDB's JSONPath is close to MySQL's JSONPath. The wildcard step ( ** ) is a non-standard extension that has the same meaning as in MySQL. The difference between MariaDB and MySQL's JSONPath is: MySQL doesn't allow one to specify the mode explicitly (but uses lax mode implicitly).
This page is licensed: CC BY-SA / Gnu FDL
Explore JSON_KEY_VALUE in MariaDB. Available from version 11.2, this function extracts key/value pairs from a JSON object, enabling easier data transformation and usage with JSON_TABLE.
JSON_KEY_VALUE(obj, json_path)JSON_KEY_VALUE extracts key/value pairs from a JSON object. The JSON path parameter is used to only return key/value pairs for matching JSON objects.
JSON_KEY_VALUE() can be used as an argument to JSON_TABLE(), which allows adding the key to a result set.
This page is licensed: CC BY-SA / Gnu FDL
Discover JSON_EQUALS in MariaDB. This function checks for equality between two JSON objects, returning 1 if they are equal, 0 if not, handling key order and data types intelligently.
Check if a value is valid JSON. This function returns 1 if the argument is a valid JSON document, and 0 otherwise.
Indicates whether the given value is a valid JSON document or not. Returns 1 if valid, 0 if not, and NULL if the argument is NULL.
The JSON_VALID function is automatically used as a for the in order to ensure that a valid json document is inserted.
The JSON_VALID function is not automatically used as a for the .
covering JSON_VALID.
This page is licensed: CC BY-SA / Gnu FDL
Remove data from a JSON document. This function removes data from a JSON document at a specified path and returns the result.
JSON_REMOVE(json_doc, path[, path] ...)Removes data from a JSON document returning the result, or NULL if any of the arguments are null. If the element does not exist in the document, no changes are made.
The function returns NULL and throws a warning if the JSON document is invalid, the path is invalid, contains a range, or contains a * or ** wildcard.
Path arguments are evaluated from left to right, with the result from the earlier evaluation being used as the value for the next.
covering JSON_REMOVE.
This page is licensed: CC BY-SA / Gnu FDL
Learn about JSON_MERGE_PATCH in MariaDB. This RFC 7396-compliant function merges JSON documents by overwriting duplicate keys, serving as a modern replacement for the deprecated JSON_MERGE.
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)Merges the given JSON documents, returning the merged result, or NULL if any argument is NULL.
JSON_MERGE_PATCH is an RFC 7396-compliant replacement for , which is deprecated.
Unlike , members with duplicate keys are not preserved.
This page is licensed: CC BY-SA / Gnu FDL
Discover JSON_OBJECT_FILTER_KEYS in MariaDB. Available from version 11.2, this function returns a new JSON object containing only the key-value pairs where the keys match those provided in a specified
JSON_OBJECT_FILTER_KEYS(obj, array_keys)JSON_OBJECT_FILTER_KEYS returns a JSON object with keys from the object that are also present in the array as string. It is used when one wants to get key-value pair such that the keys are common but the values may not be common.
This page is licensed: CC BY-SA / Gnu FDL
Check for JSON containment. This function returns 1 if a candidate JSON document is contained within a target JSON document, or 0 otherwise.
JSON_CONTAINS(json_doc, val[, path])Returns whether or not the specified value is found in the given JSON document or, optionally, at the specified path within the document. Returns 1 if it does, 0 if not and NULL if any of the arguments are null. An error occurs if the document or path is not valid, or contains the * or ** wildcards.
This page is licensed: CC BY-SA / Gnu FDL
Aggregate key-value pairs into a JSON object. This function aggregates two columns or expressions into a single JSON object.
JSON_OBJECTAGG(key, value)JSON_OBJECTAGG returns a JSON object containing key-value pairs. It takes two expressions that evaluate to a single value, or two column names, as arguments, the first used as a key, and the second as a value.
The maximum returned length in bytes is determined by the server system variable.
Returns NULL in the case of an error, or if the result contains no rows.
JSON_OBJECTAGG cannot currently be used as a .
This page is licensed: CC BY-SA / Gnu FDL
Search for a value in a JSON document. This function returns the path to the given string within a JSON document.
JSON_SEARCH(json_doc, return_arg, search_str[, escape_char[, path] ...])Returns the path to the given string within a JSON document, or NULL if any of json_doc, search_str or a path argument is NULL; if the search string is not found, or if no path exists within the document.
A warning will occur if the JSON document is not valid, any of the path arguments are not valid, if return_arg is neither one nor all, or if the escape character is not a constant. NULL will be returned.
return_arg can be one of two values:
'one: Terminates after finding the first match, so will return one path string. If there is more than one match, it is undefined which is considered first.
all: Returns all matching path strings, without duplicates. Multiple strings are autowrapped as an array. The order is undefined.
This page is licensed: CC BY-SA / Gnu FDL
Explore JSON_ARRAY_APPEND in MariaDB. This function appends values to the end of specified arrays within a JSON document, returning the modified result.
JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)Appends values to the end of the specified arrays within a JSON document, returning the result, or NULL if any of the arguments are NULL.
Evaluation is performed from left to right, with the resulting document from the previous pair becoming the new value against which the next pair is evaluated.
If the json_doc is not a valid JSON document, or if any of the paths are not valid, or contain a * or ** wildcard, an error is returned.
This page is licensed: CC BY-SA / Gnu FDL
Insert or update data in a JSON document. This function inserts or updates data in a JSON document at a specified path and returns the result.
JSON_SET(json_doc, path, val[, path, val] ...)Updates or inserts data into a JSON document, returning the result, or NULL if any of the arguments are NULL or the optional path fails to find an object.
An error will occur if the JSON document is invalid, the path is invalid or if the path contains a * or a wildcard**.**
JSON_SET can update or insert data, while can only update, and only insert.
This page is licensed: CC BY-SA / Gnu FDL
Extract data from a JSON document. This function returns data from a JSON document selected by a given path.
Quote a string as a JSON value. This function wraps a string with double quotes and escapes special characters to create a valid JSON string literal.
path : ['lax'] '$' [step]*JSON_VALID(value)SELECT JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]');
+-----------------------------------------------------------------------------+
| JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]') |
+-----------------------------------------------------------------------------+
| [{"key": "key1", "value": "val1"}, {"key": "key2", "value": "val2"}] |
+-----------------------------------------------------------------------------+SET @json1 = '[1, 2]';
SET @json2 = '[2, 3]';
SELECT JSON_MERGE_PATCH(@json1,@json2),JSON_MERGE_PRESERVE(@json1,@json2);
+---------------------------------+------------------------------------+
| JSON_MERGE_PATCH(@json1,@json2) | JSON_MERGE_PRESERVE(@json1,@json2) |
+---------------------------------+------------------------------------+
| [2, 3] | [1, 2, 2, 3] |
+---------------------------------+------------------------------------+SET @obj1= '{ "a": 1, "b": 2, "c": 3}';
SET @obj2= '{"b" : 10, "c": 20, "d": 30}';
SELECT JSON_OBJECT_FILTER_KEYS (@obj1, JSON_ARRAY_INTERSECT(JSON_KEYS(@obj1), JSON_KEYS(@obj2)));
+-------------------------------------------------------------------------------------------+
| JSON_OBJECT_FILTER_KEYS (@obj1, JSON_ARRAY_INTERSECT(JSON_KEYS(@obj1), JSON_KEYS(@obj2))) |
+-------------------------------------------------------------------------------------------+
| {"b": 2, "c": 3} |
+-------------------------------------------------------------------------------------------+SET @json = '{"A": 0, "B": {"C": 1}, "D": 2}';
SELECT JSON_CONTAINS(@json, '2', '$.A');
+----------------------------------+
| JSON_CONTAINS(@json, '2', '$.A') |
+----------------------------------+
| 0 |
+----------------------------------+
SELECT JSON_CONTAINS(@json, '2', '$.D');
+----------------------------------+
| JSON_CONTAINS(@json, '2', '$.D') |
+----------------------------------+
| 1 |
+----------------------------------+
SELECT JSON_CONTAINS(@json, '{"C": 1}', '$.A');
+-----------------------------------------+
| JSON_CONTAINS(@json, '{"C": 1}', '$.A') |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
SELECT JSON_CONTAINS(@json, '{"C": 1}', '$.B');
+-----------------------------------------+
| JSON_CONTAINS(@json, '{"C": 1}', '$.B') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+SET @json = '["A", [{"B": "1"}], {"C":"AB"}, {"D":"BC"}]';
SELECT JSON_SEARCH(@json, 'one', 'AB');
+---------------------------------+
| JSON_SEARCH(@json, 'one', 'AB') |
+---------------------------------+
| "$[2].C" |
+---------------------------------+SELECT JSON_SET(Priv, '$.locked', 'true') FROM mysql.global_privExample:
JSON path does not support negative indexes in an array.
JSON_SCHEMA_VALID allows MariaDB to support JSON schema validation. If a given json is valid against a schema it returns true. When JSON does not validate against the schema, it does not return a message about which keyword it failed against and only returns false.
The function supports JSON Schema Draft 2020 with a few exceptions:
External resources are not supported.
Hyper schema keywords are not supported.
Formats like date, email etc are treated as annotations.
To create validation rules for json field, do this:
This page is licensed: CC BY-SA / Gnu FDL
An error occurs if the JSON document is invalid, or if any of the paths are invalid or contain a * or ** wildcard.
JSON_INSERT can only insert data, while JSON_REPLACE can only update. JSON_SET can update or insert data.
JSON video tutorial covering JSON_INSERT.
This page is licensed: CC BY-SA / Gnu FDL
NULLNULLAn error occurs if any of the arguments are not valid JSON documents.
JSON_MERGE is deprecated. JSON_MERGE_PATCH is an RFC 7396-compliant replacement, and JSON_MERGE_PRESERVE is a synonym.
This page is licensed: CC BY-SA / Gnu FDL
NULLAn error will occur if the JSON document is invalid, the path is invalid or if the path contains a * or ** wildcard.
Paths and values are evaluated from left to right, with the result from the earlier evaluation being used as the value for the next.
JSON_REPLACE can only update data, while JSON_INSERT can only insert. JSON_SET can update or insert data.
JSON video tutorial covering JSON_REPLACE.
This page is licensed: CC BY-SA / Gnu FDL
json_path10NULLNULLThis page is licensed: CC BY-SA / Gnu FDL
JSON_EXISTS(json_doc, json_path)NULL.JSON_MAKE_OBJECT, the CONNECT storage engine function
This page is licensed: CC BY-SA / Gnu FDL
JSON_OBJECT([key, value[, key, value] ...])Returns NULL if no paths match or if any of the arguments are NULL.
An error occurs if any path argument is not a valid path, or if the json_doc argument is not a valid JSON document.
The path expression be a JSONPath expression as supported by MariaDB
JSON video tutorial covering JSON_EXTRACT.
This page is licensed: CC BY-SA / Gnu FDL
JSON_EXTRACT(json_doc, path[, path] ...)Returns NULL if the argument is NULL.
This page is licensed: CC BY-SA / Gnu FDL
JSON_QUOTE(json_value)SELECT JSON_QUOTE('A'), JSON_QUOTE("B"), JSON_QUOTE('"C"');
+-----------------+-----------------+-------------------+
| JSON_QUOTE('A') | JSON_QUOTE("B") | JSON_QUOTE('"C"') |
+-----------------+-----------------+-------------------+
| "A" | "B" | "\"C\"" |
+-----------------+-----------------+-------------------+SELECT jt.* FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]'),'$[*]'
COLUMNS (
k VARCHAR(20) PATH '$.key',
v VARCHAR(20) PATH '$.value',
id FOR ORDINALITY )) AS jt;
+------+------+------+
| k | v | id |
+------+------+------+
| key1 | val1 | 1 |
| key2 | val2 | 2 |
+------+------+------+SELECT JSON_EQUALS('{"a" :[1, 2, 3],"b":[4]}', '{"b":[4],"a":[1, 2, 3.0]}');
+------------------------------------------------------------------------+
| JSON_EQUALS('{"a" :[1, 2, 3],"b":[4]}', '{"b":[4],"a":[1, 2, 3.0]}') |
+------------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------------+
SELECT JSON_EQUALS('{"a":[1, 2, 3]}', '{"a":[1, 2, 3.01]}');
+------------------------------------------------------+
| JSON_EQUALS('{"a":[1, 2, 3]}', '{"a":[1, 2, 3.01]}') |
+------------------------------------------------------+
| 0 |
+------------------------------------------------------+SELECT JSON_VALID('{"id": 1, "name": "Monty"}');
+------------------------------------------+
| JSON_VALID('{"id": 1, "name": "Monty"}') |
+------------------------------------------+
| 1 |
+------------------------------------------+
SELECT JSON_VALID('{"id": 1, "name": "Monty", "oddfield"}');
+------------------------------------------------------+
| JSON_VALID('{"id": 1, "name": "Monty", "oddfield"}') |
+------------------------------------------------------+
| 0 |
+------------------------------------------------------+SELECT JSON_REMOVE('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C');
+-------------------------------------------------------+
| JSON_REMOVE('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C') |
+-------------------------------------------------------+
| {"A": 1, "B": 2} |
+-------------------------------------------------------+
SELECT JSON_REMOVE('["A", "B", ["C", "D"], "E"]', '$[1]');
+----------------------------------------------------+
| JSON_REMOVE('["A", "B", ["C", "D"], "E"]', '$[1]') |
+----------------------------------------------------+
| ["A", ["C", "D"], "E"] |
+----------------------------------------------------+SELECT * FROM t1;
+------+-------+
| a | b |
+------+-------+
| 1 | Hello |
| 1 | World |
| 2 | This |
+------+-------+
SELECT JSON_OBJECTAGG(a, b) FROM t1;
+----------------------------------------+
| JSON_OBJECTAGG(a, b) |
+----------------------------------------+
| {"1":"Hello", "1":"World", "2":"This"} |
+----------------------------------------+SET @json = '[1, 2, [3, 4]]';
SELECT JSON_ARRAY_APPEND(@json, '$[0]', 5)
+-------------------------------------+
| JSON_ARRAY_APPEND(@json, '$[0]', 5) |
+-------------------------------------+
| [[1, 5], 2, [3, 4]] |
+-------------------------------------+
SELECT JSON_ARRAY_APPEND(@json, '$[1]', 6);
+-------------------------------------+
| JSON_ARRAY_APPEND(@json, '$[1]', 6) |
+-------------------------------------+
| [1, [2, 6], [3, 4]] |
+-------------------------------------+
SELECT JSON_ARRAY_APPEND(@json, '$[1]', 6, '$[2]', 7);
+------------------------------------------------+
| JSON_ARRAY_APPEND(@json, '$[1]', 6, '$[2]', 7) |
+------------------------------------------------+
| [1, [2, 6], [3, 4, 7]] |
+------------------------------------------------+
SELECT JSON_ARRAY_APPEND(@json, '$', 5);
+----------------------------------+
| JSON_ARRAY_APPEND(@json, '$', 5) |
+----------------------------------+
| [1, 2, [3, 4], 5] |
+----------------------------------+
SET @json = '{"A": 1, "B": [2], "C": [3, 4]}';
SELECT JSON_ARRAY_APPEND(@json, '$.B', 5);
+------------------------------------+
| JSON_ARRAY_APPEND(@json, '$.B', 5) |
+------------------------------------+
| {"A": 1, "B": [2, 5], "C": [3, 4]} |
+------------------------------------+SET @json= '[1, 2, 3, 4, 5]';
SELECT JSON_EXTRACT(@json, '$[4 to 2]');
+-----------------------------------+
| JSON_EXTRACT(@json, '$[4 to 2]') |
+-----------------------------------+
| NULL |
+-----------------------------------+SELECT json_extract(@json_doc, '$**.price');SELECT json_extract(@json_doc, '$**[2]');SET @json='{
"A": [0,
[1, 2, 3],
[4, 5, 6],
"seven",
0.8,
true,
false,
"eleven",
[12, [13, 14], {"key1":"value1"},[15]],
true],
"B": {"C": 1},
"D": 2
}';
SELECT JSON_EXTRACT(@json, '$.A[-8][1]');
+--------------------------------------------------+
| JSON_EXTRACT(@json, '$.A[-8][1]') |
+--------------------------------------------------+
| 5 |
+--------------------------------------------------+
SELECT JSON_EXTRACT(@json, '$.A[last-7][1]');
+-----------------------------------------------+
| SELECT JSON_EXTRACT(@json, '$.A[last-7][1]'); |
+-----------------------------------------------+
| 5 |
+-----------------------------------------------+
SET @json= '[
[1, {"key1": "value1"}, 3],
[false, 5, 6],
[7, 8, [9, {"key2": 2}, 11]],
[15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
[19, 20],
21, 22
]';
SELECT JSON_EXTRACT(@json, '$[0 to 3][2]');
+-----------------------------------------------+
| JSON_EXTRACT(@json, '$[0 to 3][2]') |
+-----------------------------------------------+
| [3, 6, [9, {"key2": 2}, 11], [14]] |
+-----------------------------------------------+JSON_SCHEMA_VALID(schema, json);CREATE TABLE obj_table(val_obj JSON CHECK(JSON_SCHEMA_VALID('{
"type":"object",
"properties": {
"number1":{
"type":"number",
"maximum":5,
"const":4
},
"string1":{
"type":"string",
"maxLength":5,
"minLength":3
},
"object1":{
"type":"object",
"properties":{
"key1": {"type":"string"},
"key2":{"type":"array"},
"key3":{"type":"number", "minimum":3}
},
"dependentRequired": { "key1":["key3"] }
}
},
"required":["number1","object1"]
}', val_obj)));
INSERT INTO obj_table VALUES(
'{"number1":4, "string1":"abcd",
"object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}'
);
INSERT INTO obj_table VALUES(
'{"number1":3, "string1":"abcd",
"object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}'
);
ERROR 4025 (23000): CONSTRAINT `obj_table.val_obj` failed for `test`.`obj_table`
SELECT * FROM obj_table;
+--------------------------------------------------------------------------------------------------+
| val_obj |
+--------------------------------------------------------------------------------------------------+
| {"number1":4, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}} |
+--------------------------------------------------------------------------------------------------+
SET @schema= '{
"properties" : {
"number1":{ "maximum":10 },
"string1" : { "maxLength": 3}
}
}';
SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }');
+----------------------------------------------------------------+
| JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }') |
+----------------------------------------------------------------+
| 0 |
+----------------------------------------------------------------+
SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }');
+----------------------------------------------------------------+
| JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }') |
+----------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------+JSON_INSERT(json_doc, path, val[, path, val] ...)SET @json = '{ "A": 0, "B": [1, 2]}';
SELECT JSON_INSERT(@json, '$.C', '[3, 4]');
+--------------------------------------+
| JSON_INSERT(@json, '$.C', '[3, 4]') |
+--------------------------------------+
| { "A": 0, "B": [1, 2], "C":"[3, 4]"} |
+--------------------------------------+JSON_MERGE(json_doc, json_doc[, json_doc] ...)SET @json1 = '[1, 2]';
SET @json2 = '[3, 4]';
SELECT JSON_MERGE(@json1,@json2);
+---------------------------+
| JSON_MERGE(@json1,@json2) |
+---------------------------+
| [1, 2, 3, 4] |
+---------------------------+JSON_REPLACE(json_doc, path, val[, path, val] ...)SELECT JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4);
+-----------------------------------------------------+
| JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4) |
+-----------------------------------------------------+
| { "A": 1, "B": [2, 4]} |
+-----------------------------------------------------+SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2");
+------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key3");
+------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key3") |
+------------------------------------------------------------+
| 0 |
+------------------------------------------------------------+
SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]");
+---------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]");
+----------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]") |
+----------------------------------------------------------------+
| 0 |
+----------------------------------------------------------------+SELECT JSON_OBJECT("id", 1, "name", "Monty");
+---------------------------------------+
| JSON_OBJECT("id", 1, "name", "Monty") |
+---------------------------------------+
| {"id": 1, "name": "Monty"} |
+---------------------------------------+SET @json = '[1, 2, [3, 4]]';
SELECT JSON_EXTRACT(@json, '$[1]');
+-----------------------------+
| JSON_EXTRACT(@json, '$[1]') |
+-----------------------------+
| 2 |
+-----------------------------+
SELECT JSON_EXTRACT(@json, '$[2]');
+-----------------------------+
| JSON_EXTRACT(@json, '$[2]') |
+-----------------------------+
| [3, 4] |
+-----------------------------+
SELECT JSON_EXTRACT(@json, '$[2][1]');
+--------------------------------+
| JSON_EXTRACT(@json, '$[2][1]') |
+--------------------------------+
| 4 |
+--------------------------------+Return the type of a JSON value. This function returns a string indicating the type of a JSON value (e.g., OBJECT, ARRAY, INTEGER).
JSON_TYPE(json_val)Returns the type of a JSON value (as a string), or NULL if the argument is null.
An error will occur if the argument is an invalid JSON value.
The following is a complete list of the possible return types:
This page is licensed: CC BY-SA / Gnu FDL
Extract a scalar value from a JSON document. This function extracts a value from a JSON document at a given path and returns it as a scalar.
JSON_VALUE(json_doc, path)Given a JSON document, returns the scalar specified by the path. Returns NULL if not given a valid JSON document, or if there is no match.
In the SET statement below, two escape characters are needed, as a single escape character would be applied by the SQL parser in the SET statement, and the escaped character would not form part of the saved value.
This page is licensed: CC BY-SA / Gnu FDL
INTEGER
A number without a floating point decimal.
1
NULL
JSON null literal (this is returned as a string, not to be confused with the SQL NULL value!)
null
STRING
JSON String
"a sample string"
ARRAY
JSON array
[1, 2, {"key": "value"}]
OBJECT
JSON object
{"key":"value"}
BOOLEAN
JSON true/false literals
true, false
DOUBLE
A number with at least one floating point decimal.
1.2
SELECT json_value('{"key1":123}', '$.key1');
+--------------------------------------+
| json_value('{"key1":123}', '$.key1') |
+--------------------------------------+
| 123 |
+--------------------------------------+
SELECT json_value('{"key1": [1,2,3], "key1":123}', '$.key1');
+-------------------------------------------------------+
| json_value('{"key1": [1,2,3], "key1":123}', '$.key1') |
+-------------------------------------------------------+
| 123 |
+-------------------------------------------------------+SELECT JSON_TYPE('{"A": 1, "B": 2, "C": 3}');
+---------------------------------------+
| JSON_TYPE('{"A": 1, "B": 2, "C": 3}') |
+---------------------------------------+
| OBJECT |
+---------------------------------------+SET @json = '{"key1":"60\\" Table", "key2":"1"}';
SELECT JSON_VALUE(@json,'$.key1') AS Name , json_value(@json,'$.key2') as ID;
+-----------+------+
| Name | ID |
+-----------+------+
| 60" Table | 1 |
+-----------+------+Unquote a JSON value. This function removes the quotes from a JSON string and unescapes special characters.
JSON_UNQUOTE(val)Unquotes a JSON value, returning a string, or NULL if the argument is null.
An error will occur if the given value begins and ends with double quotes and is an invalid JSON string literal.
If the given value is not a JSON string, value is passed through unmodified.
Certain character sequences have special meanings within a string. Usually, a backslash is ignored, but the escape sequences in the table below are recognised by MariaDB, unless the is set to NO_BACKSLASH_ESCAPES .
With the default :
Setting NO_BACKSLASH_ESCAPES:
This page is licensed: CC BY-SA / Gnu FDL
\
Backslash ()
\uXXXX
UTF-8 bytes for Unicode value XXXX
"
Double quote (")
\b
Backslash
\f
Formfeed
\n
Newline (linefeed)
\r
Carriage return
\t
Tab
SELECT JSON_UNQUOTE('"Monty"');
+-------------------------+
| JSON_UNQUOTE('"Monty"') |
+-------------------------+
| Monty |
+-------------------------+SELECT JSON_UNQUOTE('Si\bng\ting');
+-----------------------------+
| JSON_UNQUOTE('Si\bng\ting') |
+-----------------------------+
| Sng ing |
+-----------------------------+SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT JSON_UNQUOTE('Si\bng\ting');
+-----------------------------+
| JSON_UNQUOTE('Si\bng\ting') |
+-----------------------------+
| Si\bng\ting |
+-----------------------------+Convert JSON data to a relational table. This table function extracts data from a JSON document and returns it as a relational table with columns.
JSON_TABLE is a table function that converts JSON data into a relational form.
JSON_TABLE can be used in contexts where a table reference can be used; in the FROM clause of a statement, and in multi-table / statements.
json_doc is the JSON document to extract data from. In the simplest case, it is a string literal containing JSON. In more complex cases it can be an arbitrary expression returning JSON. The expression may have references to columns of other tables. However, one can only refer to tables that precede this JSON_TABLE invocation. For RIGHT JOIN, it is assumed that its outer side precedes the inner. All tables in outer selects are also considered preceding.
context_path is a expression pointing to a collection of nodes in json_doc that will be used as the source of rows.
The COLUMNS clause declares the names and types of the columns that JSON_TABLE returns, as well as how the values of the columns are produced.
The following types of columns are supported:
Locates the JSON node pointed to by path_str and returns its value. The path_str is evaluated using the current row source node as the context node.
The on_empty and on_error clauses specify the actions to be performed when the value was not found or there was an error condition. See the ON EMPTY and ON ERROR clauses section for details.
Counts the rows, starting from 1.
Example:
Checks whether the node pointed to by value_path exists. The value_path is evaluated using the current row source node as the context node.
NESTED PATH converts nested JSON structures into multiple rows.
It finds the sequence of JSON nodes pointed to by path and uses it to produce rows. For each found node, a row is generated with column values as specified by the NESTED PATH's COLUMNS clause. If path finds no nodes, only one row is generated with all columns having NULL values.
For example, consider a JSON document that contains an array of items, and each item, in turn, is expected to have an array of its available sizes:
NESTED PATH allows one to produce a separate row for each size each item has:
NESTED PATH clauses can be nested within one another. They can also be located next to each other. In that case, the nested path clauses will produce records one at a time. The ones that are not producing records will have all columns set to NULL.
Example:
The ON EMPTY clause specifies what will be done when the element specified by the search path is missing in the JSON document.
When ON EMPTY clause is not present, NULL ON EMPTY is implied.
The ON ERROR clause specifies what should be done if a JSON structure error occurs when trying to extract the value pointed to by the path expression. A JSON structure error here occurs only when one attempts to convert a JSON non-scalar (array or object) into a scalar value. When the ON ERROR clause is not present, NULL ON ERROR is implied.
Note: A datatype conversion error (e.g. attempt to store a non-integer value into an field, or a column being truncated) is not considered a JSON error and so will not trigger the ON ERROR behavior. It will produce warnings, in the same way as would.
In the current code, evaluation of JSON_TABLE is deterministic, that is, for a given input string JSON_TABLE will always produce the same set of rows in the same order. However, one can think of JSON documents that one can consider identical which will produce different output. In order to be future-proof and withstand changes like
sorting JSON object members by name (like MySQL does);
changing the way duplicate object members are handled the function is marked as .
JSON_TABLE does not allow to extract a JSON "subdocument" into a JSON column.
(video)
This page is licensed: CC BY-SA / Gnu FDL
JSON_TABLE(json_doc,
context_path COLUMNS (column_list)
) [AS] aliascolumn_list:
column[, column][, ...]column:
name FOR ORDINALITY
| name type PATH path_str [on_empty] [on_error]
| name type EXISTS PATH path_str
| NESTED PATH path_str COLUMNS (column_list)on_empty:
{NULL | DEFAULT string | ERROR} ON EMPTYon_error:
{NULL | DEFAULT string | ERROR} ON ERRORname type PATH path_str [on_empty] [on_error]SET @json='
[
{"name":"Laptop", "color":"black", "price":"1000"},
{"name":"Jeans", "color":"blue"}
]';
SELECT * FROM json_table(@json, '$[*]'
COLUMNS(
name VARCHAR(10) path '$.name',
color VARCHAR(10) path '$.color',
price DECIMAL(8,2) path '$.price' )
) AS jt;
+--------+-------+---------+
| name | color | price |
+--------+-------+---------+
| Laptop | black | 1000.00 |
| Jeans | blue | NULL |
+--------+-------+---------+name FOR ORDINALITYset @json='
[
{"name":"Laptop", "color":"black"},
{"name":"Jeans", "color":"blue"}
]';
select * from json_table(@json, '$[*]'
columns(
id for ordinality,
name varchar(10) path '$.name')
) as jt;
+------+--------+
| id | name |
+------+--------+
| 1 | Laptop |
| 2 | Jeans |
+------+--------+name type EXISTS PATH path_strset @json='
[
{"name":"Laptop", "color":"black", "price":1000},
{"name":"Jeans", "color":"blue"}
]';
select * from json_table(@json, '$[*]'
columns(
name varchar(10) path '$.name',
has_price integer exists path '$.price')
) as jt;
+--------+-----------+
| name | has_price |
+--------+-----------+
| Laptop | 1 |
| Jeans | 0 |
+--------+-----------+NESTED PATH path COLUMNS (column_list)SET @json='
[
{"name":"Jeans", "sizes": [32, 34, 36]},
{"name":"T-Shirt", "sizes":["Medium", "Large"]},
{"name":"Cellphone"}
]';select * from json_table(@json, '$[*]'
columns(
name varchar(10) path '$.name',
nested path '$.sizes[*]' columns (
size varchar(32) path '$'
)
)
) as jt;
+-----------+--------+
| name | size |
+-----------+--------+
| Jeans | 32 |
| Jeans | 34 |
| Jeans | 36 |
| T-Shirt | Medium |
| T-Shirt | Large |
| Cellphone | NULL |
+-----------+--------+set @json='
[
{"name":"Jeans", "sizes": [32, 34, 36], "colors":["black", "blue"]}
]';
select * from json_table(@json, '$[*]'
columns(
name varchar(10) path '$.name',
nested path '$.sizes[*]' columns (
size varchar(32) path '$'
),
nested path '$.colors[*]' columns (
color varchar(32) path '$'
)
)
) as jt;
+-------+------+-------+
| name | size | color |
+-------+------+-------+
| Jeans | 32 | NULL |
| Jeans | 34 | NULL |
| Jeans | 36 | NULL |
| Jeans | NULL | black |
| Jeans | NULL | blue |
+-------+------+-------+on_empty:
{NULL | DEFAULT string | ERROR} ON EMPTYon_error:
{NULL | DEFAULT string | ERROR} ON ERRORSELECT * FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path '$.foo') ) AS T;
+-----------+
| jscol |
+-----------+
| [1,2,3,4] |
+-----------+SELECT * FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path '$.foo') ) AS T;
+-------+
| jscol |
+-------+
| NULL |
+-------+