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.
Create a JSON array. This function evaluates a list of values and returns a JSON array containing those values.
Check for JSON containment. This function returns 1 if a candidate JSON document is contained within a target JSON document, or 0 otherwise.
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:
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.
This page is licensed: CC BY-SA / Gnu FDL
0NULL***This page is licensed: CC BY-SA / Gnu FDL
JSON_CONTAINS(json_doc, val[, path])NULLEvaluation 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
JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)Checks if there is equality between two json objects. Returns 1 if it there is, 0 if not, or NULL if any of the arguments are null.
This page is licensed: CC BY-SA / Gnu FDL
JSON_EQUALS(json1, json2)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)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"}}]] |
+-----------------------------------------------------------------------+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] |
+--------------------------------------------------+SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Monty" }';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 |
+----------------------------+----------------------------+Discover JSON_OVERLAPS in MariaDB. Available from version 10.9, this function compares two JSON documents, returning true if they share at least one common key-value pair, array element, or scalar val
JSON_OVERLAPS(json_doc1, json_doc2)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
Explore JSON_DETAILED in MariaDB. Also known as JSON_PRETTY, this function formats JSON documents with indentation and newlines to emphasize nested structures for readability.
Extract data from a JSON document. This function returns data from a JSON document selected by a given path.
JSON_EXTRACT(json_doc, path[, path] ...)Extracts data from a JSON document. The extracted data is selected from the parts matching the path arguments. Returns all matched values; either as a single matched value, or, if the arguments could return multiple values, a result autowrapped as an array in the matching order.
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 as supported by MariaDB
covering JSON_EXTRACT.
This page is licensed: CC BY-SA / Gnu FDL
Aggregate values into a JSON array. This function aggregates a result set column into a single JSON array.
JSON_ARRAYAGG(column_or_expression)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 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 .
The full syntax is as follows:
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
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.
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.
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 .
This page is licensed: CC BY-SA / Gnu FDL
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 = '[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]] |
+------------------------------------------------+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 |
+------------------------------------------------------+JSON_DETAILED(json_doc[, tab_size])
JSON_PRETTY(json_doc[, tab_size])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
JSON_LENGTH(json_doc[, path])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] ...)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
JSON_VALUE(json_doc, path)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 |
+-------------------------------------------------------+NULLJSON_MERGE_PATCH is an RFC 7396-compliant replacement for JSON_MERGE, which is deprecated.
Unlike JSON_MERGE_PRESERVE, members with duplicate keys are not preserved.
This page is licensed: CC BY-SA / Gnu FDL
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)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] |
+---------------------------------+------------------------------------+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
JSON video tutorial covering JSON_COMPACT.
This page is licensed: CC BY-SA / Gnu FDL
JSON_COMPACT(json_doc)SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, [1]]') AS is_overlap;
+--------------------- +
| is_overlap |
+----------------------+
| 0 |
+----------------------+JSON_ARRAYAGG([DISTINCT] expr
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}])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"}] |
+-----------------------------------------------------------------------------+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
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
Available from version 11.2, this function compares two JSON arrays and returns a new array containing only the items present in both.
Learn about the JSON_INSERT function in MariaDB. This function inserts new data into a JSON document without replacing existing values, returning the updated document or NULL.
JSON_INSERT(json_doc, path, val[, path, val] ...)Inserts data into a JSON document, returning the resulting document or NULL if either of the json_doc or path arguments are null.
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 can only update. can update or insert data.
covering JSON_INSERT.
This page is licensed: CC BY-SA / Gnu FDL
Extract a JSON object or array. This function extracts data from a JSON document at a given path, returning a JSON object or array.
Create a JSON object. This function evaluates a list of key-value pairs and returns a JSON object containing those pairs.
JSON_OBJECT([key, value[, key, value] ...])Returns a JSON object containing the given key/value pairs. The key/value list can be empty.
An error will occur if there are an odd number of arguments, or any key name is NULL.
, the CONNECT storage engine function
This page is licensed: CC BY-SA / Gnu FDL
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.
JSON_QUOTE(json_value)Quotes a string as a JSON value, usually for producing valid JSON string literals for inclusion in JSON documents. Wraps the string with double quote characters and escapes interior quotes and other special characters, returning a utf8mb4 string.
Returns NULL if the argument is NULL.
This page is licensed: CC BY-SA / Gnu FDL
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 = '{"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 |
+------------------------------------------------+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 |
+-----------+------+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 @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 |
+---------------------+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 @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 |
+--------------------------------+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] |
+------------------+------------------+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 |
+------+------+------+path : ['lax'] '$' [step]*Example:
JSON path does not support negative indexes in an array.
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.
JSON video tutorial covering JSON_REMOVE.
This page is licensed: CC BY-SA / Gnu FDL
JSON_REMOVE(json_doc, path[, path] ...)NULLAn 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 JSON_REPLACE can only update, and JSON_INSERT only insert.
This page is licensed: CC BY-SA / Gnu FDL
JSON_SET(json_doc, path, val[, path, val] ...)SELECT JSON_SET(Priv, '$.locked', 'true') FROM mysql.global_privNULLEvaluation 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
JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)NULLA 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
JSON_SEARCH(json_doc, return_arg, search_str[, escape_char[, path] ...])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
JSON_OBJECT_FILTER_KEYS(obj, array_keys)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} |
+-------------------------------------------------------------------------------------------+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] ...)This page is licensed: CC BY-SA / Gnu FDL
JSON_LOOSE(json_doc)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]} |
+-----------------------+--------------------+SET @json1= '[1,2,3]';
SET @json2= '[1,2,4]';
SELECT json_array_intersect(@json1, @json2);
+--------------------------------------+
| json_array_intersect(@json1, @json2) |
+--------------------------------------+
| [1, 2] |
+--------------------------------------+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] |
+-------------------------------------------------------+SELECT JSON_OBJECT("id", 1, "name", "Monty");
+---------------------------------------+
| JSON_OBJECT("id", 1, "name", "Monty") |
+---------------------------------------+
| {"id": 1, "name": "Monty"} |
+---------------------------------------+SELECT JSON_QUOTE('A'), JSON_QUOTE("B"), JSON_QUOTE('"C"');
+-----------------+-----------------+-------------------+
| JSON_QUOTE('A') | JSON_QUOTE("B") | JSON_QUOTE('"C"') |
+-----------------+-----------------+-------------------+
| "A" | "B" | "\"C\"" |
+-----------------+-----------------+-------------------+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
Merge JSON documents. This function merges two or more JSON documents into a single JSON document, preserving all keys and values.
JSON_MERGE(json_doc, json_doc[, json_doc] ...)Merges the given JSON documents.
Returns the merged result, or NULL if any argument is NULL.
An error occurs if any of the arguments are not valid JSON documents.
JSON_MERGE is deprecated. is an RFC 7396-compliant replacement, and is a synonym.
This page is licensed: CC BY-SA / Gnu FDL
Explore JSON_EXISTS in MariaDB. This function checks whether a specified JSON document contains an element at a given path, returning 1 for existence and 0 otherwise.
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
Replace values in a JSON document. This function replaces existing values in a JSON document and returns the result.
JSON_REPLACE(json_doc, path, val[, path, val] ...)Replaces existing values in a JSON document, returning the result, or NULL if any of the arguments are NULL.
An 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 can only insert. can update or insert data.
covering JSON_REPLACE.
This page is licensed: CC BY-SA / Gnu FDL
Discover JSON_NORMALIZE in MariaDB. This function recursively sorts keys and removes spaces from a JSON document, enabling reliable equality comparisons and unique constraints on JSON data.
JSON_NORMALIZE(json)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
Return keys from a JSON object. This function returns the keys from the top-level value of a JSON object as a JSON array.
JSON_KEYS(json_doc[, path])Returns the keys as a JSON array from the top-level value of a JSON object or, if the optional path argument is provided, the top-level keys from the path.
Excludes keys from nested sub-objects in the top level value. The resulting array will be empty if the selected object is empty.
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
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]] |
+-----------------------------------------------+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"] |
+----------------------------------------------------+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 = '["A", [{"B": "1"}], {"C":"AB"}, {"D":"BC"}]';
SELECT JSON_SEARCH(@json, 'one', 'AB');
+---------------------------------+
| JSON_SEARCH(@json, 'one', 'AB') |
+---------------------------------+
| "$[2].C" |
+---------------------------------+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] |
+---------------------------------+------------------------------------+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 = '{ "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_VALID(value)\
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_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"] |
+-----------------------------------------------------+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 |
+-----------------------------+SET @json1 = '[1, 2]';
SET @json2 = '[3, 4]';
SELECT JSON_MERGE(@json1,@json2);
+---------------------------+
| JSON_MERGE(@json1,@json2) |
+---------------------------+
| [1, 2, 3, 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_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_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4);
+-----------------------------------------------------+
| JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4) |
+-----------------------------------------------------+
| { "A": 1, "B": [2, 4]} |
+-----------------------------------------------------+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'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
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
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
SELECT JSON_TYPE('{"A": 1, "B": 2, "C": 3}');
+---------------------------------------+
| JSON_TYPE('{"A": 1, "B": 2, "C": 3}') |
+---------------------------------------+
| OBJECT |
+---------------------------------------+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_TABLE can be used in contexts where a table reference can be used; in the FROM clause of a SELECT statement, and in multi-table UPDATE/DELETE 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 JSON Path 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 integer field, or a varchar 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 CAST(value AS datatype) 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 unsafe for statement-based replication.
JSON_TABLE does not allow to extract a JSON "subdocument" into a JSON column.
JSON Support (video)
This page is licensed: CC BY-SA / Gnu FDL
SELECT * 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 |
+-------+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 ERROR