SoFunction
Updated on 2025-04-06

Easily get started with JSON functions of MYSQL to achieve efficient data query and operation

1. JSON_EXTRACT Extracts specified data

MySQL JSON_EXTRACT()The function extracts the data specified by the path expression in the JSON document and returns it.

grammar:

JSON_EXTRACT(json, path, ...)

Parameter description:

  • json: Required. A JSON document.
  • path: Required. Specify at least one path expression.

Return value:

  • Returns all values ​​in the JSON document that are matched by the path expression.
  • Return to NULL:
    • The specified path does not exist.
    • Any parameter isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression.

Example:

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[1]'); // 2
SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]'); // {"x": 3}

2. JSON_UNQUOTE Cancel double quotes

MySQL JSON_UNQUOTE()The function unquotes the JSON value and returns the result as a string.

grammar:

JSON_UNQUOTE(json_val)

Parameter description:

  • json_val：Required. A string.

Return value:

  • Unquote JSON values
  • Return to NULL: the parameter isNULL。
  • Error status:Not a valid JSON string literal. Can recognize escaped characters in the following table:
    • \": Double quotes"
    • \b：Backspace characters
    • \f：Page changer
    • \n：Line breaks
    • \r：Carriage return symbol
    • \t：Tab symbols
    • \\：Backslash \
    • \uXXXX：UTF-8 bytes of Unicode value XXXX

Example:

SELECT JSON_UNQUOTE('"123456"');    // 123456
SELECT JSON_UNQUOTE(CAST('"cxian"' AS JSON));    // cxian

3. JSON_KEYS takes the array of members

MySQL JSON_KEYS()The function returns an array containing the uppermost member (key) in the specified JSON object.

grammar:

JSON_KEYS(json)
JSON_KEYS(json, path)

Parameter description:

  • json: Required. A JSON object document.

  • path: Optional. Path expression.

Return value:

  • Returns an array containing the uppermost member (key) in the specified JSON object.
  • If a path expression is specified, it returns an array of uppermost members in the JSON object matching the path expression.
  • returnNULL situation:
    • Without a path, the JSON document is not a JSON object.
    • There is a path, and the JSON value matching the path is not a JSON object.
    • Any parameter isNULL。
  • Error status:
    •  jsonNot a valid JSON document.
    • pathNot a valid path expression.

Example:

SELECT JSON_KEYS('{"a": 1, "b": 2, "c": 3}');              // ["a", "b", "c"]
SELECT JSON_KEYS('[1, {"a": 1, "b": 2, "c": 3}]', '$[1]'); // ["a", "b", "c"]

SELECT
    JSON_KEYS('1'),         // null
    JSON_KEYS('"true"'),    // null
    JSON_KEYS('"abc"'),     // null
    JSON_KEYS('[0, 1]');    // null

4. JSON_ARRAY converts parameters into arrays

MySQL JSON_ARRAY()The function returns a JSON array containing all parameters.

grammar:

JSON_ARRAY(value1[, value2[, ...]])

Parameter description:

  • value1[, value2[, ...]]: Optional. Some values, they will be placed in the JSON array.

Return value:

  • A JSON array containing all parameters.
  • Value conversion situation:
    • TRUE is converted to true
    • FALSE is converted to false
    • NULL is converted to null
    • Date, time, date time is converted to string

Example:

SELECT JSON_ARRAY(1, '1', NULL, TRUE, FALSE, NOW()); // [1, "1", null, true, false, "2023-11-05 16:58:34.000000"]
SELECT JSON_ARRAY(JSON_ARRAY(1, 2), JSON_ARRAY('a', 'b')); // [[1, 2], ["a", "b"]]

5. Convert JSON_OBJECT parameter to object

MySQL JSON_OBJECT()The function returns a JSON object containing all key-value pairs specified by the parameter.

grammar:

JSON_OBJECT(key, value[, key2, value2, ...])

Parameter description:

  • key: Required. Key in the object.
  • value: Required. The value of the key in the object.

Return value:

  • A JSON object containing all key-value pairs.
  • Error status:
    • keyyesNULL。
    • Odd number of parameters.

Example:

SELECT JSON_OBJECT('name', 'cxian', 'age', 22); // {"age": 22, "name": "cxian"}
SELECT JSON_OBJECT('name', 'cxian', 'age', 22, 'age', 33); // {"age": 33, "name": "cxian"}

6. JSON_DEPTH Get the depth of JSON

MySQL JSON_DEPTH()The function returns the maximum depth of a JSON document.

grammar:

JSON_DEPTH(json)

Parameter description:

  • json: Required. A JSON document.

Return value:

  • The maximum depth of a JSON document. The rules are as follows:
    • The depth of an empty array, an empty object, or a pure value is 1.
    • The depth of an array containing only elements with depth 1 is 2.
    • The depth of an object whose value has a depth of 1 is 2.
    • The depth of other JSON documents is greater than 2.
  • returnNULL situation:The parameters areNULL。
  • Error status:The parameters are not valid JSON documents.

Example:

SELECT 
    JSON_DEPTH('[]'),             // 1
    JSON_DEPTH('[1, 2]'),         // 2 
    JSON_DEPTH('[1, [2, 3]]');    // 3

7. JSON_LENGTH takes the node length

MySQL JSON_LENGTH()The function returns the length of the node specified by the path in the JSON document or the JSON document.

grammar:

JSON_LENGTH(json)
JSON_LENGTH(json, path)

Parameter description:

  • json: Required. A JSON document.

  • path: Optional. A path expression.

Return value:

  • There is path: Returns the length of the value specified by the path in the JSON document.
  • No path: Returns the length of the JSON document.
  • Rules for calculating the length of a JSON document:
    • The length of the pure value is 1.
    • The length of an array is the number of elements of the array.
    • The length of an object is the number of object members.
    • Embedded arrays or objects do not participate in calculating lengths.
  • returnNULL situation:
    • The specified path does not exist.
    • Any parameter isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression.
    • pathIncluded*or**。

Example:

SELECT
    JSON_LENGTH('1'),        // 1
    JSON_LENGTH('true'),     // 1
    JSON_LENGTH('false'),    // 1
    JSON_LENGTH('null'),     // 1
    JSON_LENGTH('"a"');      // 1
    JSON_LENGTH('[]'),       // 0
    JSON_LENGTH('[1, 2]'),   // 2
    JSON_LENGTH('[1, {"x": 2}]');    // 2

8. JSON_CONTAINS determines whether it contains

MySQL JSON_CONTAINS()Functions check whether a JSON document contains another JSON document.

grammar:

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)

Parameter description:

  • target_jsonRequired. A JSON document.

  • candidate_json: Required. The included JSON document.

  • path: Optional. A path expression.

Return value:

  • Contains: Return 1.
  • Not included: Return 0.
  • returnNULL situation:
    • The specified path does not exist in the JSON document.
    • Any parameter isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression.

Example:

SELECT
    JSON_CONTAINS('[1, 2, {"x": 3}]', '1'),        // 1
    JSON_CONTAINS('[1, 2, {"x": 3}]', '{"x": 3}'), // 1
    JSON_CONTAINS('[1, 2, {"x": 3}]', '3'),        // 0
    JSON_CONTAINS('[1, 2, [3, 4]]', '2'),          // 1
    JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]');  // 0

9. JSON_ARRAY_APPEND Append value

MySQL JSON_ARRAY_APPEND()The function appends a value to the specified array in the JSON document and returns the modified JSON document.

grammar:

JSON_ARRAY_APPEND(json, path, value[, path2, value2] ...)

Parameter description:

  • json: Required. Modified JSON document.

  • path: Required. Add a path to the new element. A valid path expression that cannot contain*or**。

  • value: Required. The new element value added to the array.

Return value:

  • Appended JSON document.
  • returnNULL situation:The JSON document or path isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression or contains*or**。

Example:

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);      // [1, 2, 3, 4]
SELECT JSON_ARRAY_APPEND('[1, [2, 3]]', '$[0]', 4); // [[1, 4], [2, 3]]
SELECT JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4); // [1, [2, 3, 4]]
SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food"); // {"name": "Tim", "hobby": ["car", "food"]}
SELECT JSON_ARRAY_APPEND('1', '$', 2); // [1, 2]

10. JSON_ARRAY_INSERT Specify the position insertion value

MySQL JSON_ARRAY_INSERT()The function inserts a value to the specified position in the specified array in the JSON document and returns the new JSON document.

grammar:

JSON_ARRAY_INSERT(json, path, value[, path2, value2] ...)

Parameter description:

  • json：Required. Modified JSON document.

  • path：Required. Insert the array element position of the new element. A valid path expression that cannot contain*or**. for example$[0]and$.a[0]Indicates inserting a new element at the beginning of the array.

  • value: Required. The new element value inserted into the array.

Return value:

  • The inserted JSON document.
  • returnNULL situation:The JSON document or path isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression or contains*or**。
    • pathIt indicates that the path of the array element is not.

Example:

set @str = '[1, [2, 3], {"a": [4, 5]}]'
SELECT 
  JSON_ARRAY_INSERT(@str, '$[0]', 0), // [0, 1, [2, 3], {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[1]', 0), // [1, 0, [2, 3], {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[2]', 0), // [1, [2, 3], 0, {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[1][0]', 0), // [1, [0, 2, 3], {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[2].a[0]', 0); // [1, [2, 3], {"a": [0, 4, 5]}]

11. JSON_CONTAINS_PATH determines whether it exists

MySQL JSON_CONTAINS_PATH()Functions check whether there is a value on the specified path in a JSON document.

grammar:

JSON_CONTAINS_PATH(json, one_or_all, path[, path])

Parameter description:

  • json: Required. A JSON document.

  • one_or_all: Required. Available values:'one', 'all'. It indicates whether all paths are checked.

  • path: Required. You should specify at least one path expression.

Return value:

  • Returns 1 if there is a value, otherwise 0 if it is returned.
  • One_or_all parameter description:
    • 'one': There is a value on any path, returns 1, otherwise returns 0.
    • 'all': All paths have values, return 1, otherwise return 0.
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression.

Example:

SELECT
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]'), // 1
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[3]'), // 0
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[2].x'), // 1
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'one', '$[0]', '$[3]'), // 1
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]', '$[3]'); // 0

12. JSON_INSERT Insert data

MySQL JSON_INSERT()The function inserts data into a JSON document and returns a new JSON document.

grammar:

JSON_INSERT(json, path, value[, path2, value2] ...)

Parameter description:

  • json: Required. Modified JSON document.

  • path: Required. A valid path expression that cannot contain*or**。

  • value: Required. The data being inserted.

Return value:

  • The inserted JSON document. (If it already exists, it will not be inserted)
  • pathfor$: Return to the original JSON document.
  • returnNULL situation:The JSON document or path isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression or contains*or**。 

Example:

SET @arr = '[1, [2, 3], {"a": [4, 5]}]';
SELECT JSON_INSERT(@arr, '$[0]', 0, '$[3]', 6); // [1, [2, 3], {"a": [4, 5]}, 6]
SET @obj = '{"x": 1}';
SELECT JSON_INSERT(@obj, '$.y', '2');           // {"x": 1, "y": "2"}

13. JSON_MERGE Multiple JSON merge

MySQL JSON_MERGE()Functions merge two or more JSON documents and return the merged result.

grammar:

JSON_MERGE(json1, json2, ...)

Parameter description:

  • json1: Required. A JSON object document.

  • json2: Required. A JSON object document.

Return value:

  • The merged new JSON document.
  • returnNULL situation:Any parameter isNULL。
  • Error status: The parameter is not a valid JSON document.

Example:

SELECT JSON_MERGE('1', 'true', '"hello"', 'null'); // [1, true, "hello", null]
SELECT JSON_MERGE('[1, 2]', '[2, 3]');             // [1, 2, 2, 3]
SELECT JSON_MERGE('{"x": 1}', '{"x": 2, "y": 3}'); // {"x": [1, 2], "y": 3}
SELECT JSON_MERGE('{"x": 1}', '[1, 2]');           // [{"x": 1}, 1, 2]

14. JSON_MERGE_PATCH Multiple JSON replacement and merge

MySQL JSON_MERGE_PATCH()The function performs a replacement merge on two or more JSON documents and returns the merged result.

Replace merge: Only the following values ​​are retained when the same key value is.

grammar:

JSON_MERGE_PATCH(json1, json2, ...)

Parameter description:

  • json1: Required. A JSON object document.

  • json2: Required. A JSON object document.

Return value:

  • The merged JSON document.
  • The merger rules are as follows:
    • If the first parameter is not an object, the merge result is the same as the second parameter merging the empty object.
    • If the second parameter is not an object, the merge result is the second parameter.
    • If both parameters are objects, the merged object has the following members:
      • Members that exist only in the first object.
      • Only exists in the second object and the value is notnullMember of  .
      • Exist in the second object and the value is notnull, and there are corresponding members of the same key in the first object.
  • returnNULL case: Any parameter is NULL.
  • Error status:Any parameter is not a valid JSON document.

Example:

SELECT
    JSON_MERGE_PATCH('2', 'true'),    // true
    JSON_MERGE_PATCH('[1, 2]', '[2, 3]'),    // [2, 3]
	JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}'), // {"x": 2, "y": 3, "z": 7}
	JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}'); // {"x": 2}

15. JSON_MERGE_PRESERVE Multiple JSON merge

MySQL JSON_MERGE_PRESERVE()Functions merge two or more JSON documents and return the merged result.

This function is used the same as JSON_MERGE_PATCH(), but the merge logic is different.

The different rules are merged as follows:

  • Combine two arrays into one array, retaining all elements in the array.
  • The two objects are merged into one object, retaining all keys and values.
  • A pure value is wrapped into an array and merged as an array.
  • When objects and arrays are combined, the objects are wrapped into an array and merged as an array.

Example:

SELECT
    JSON_MERGE_PRESERVE('2', 'true'), // [2, true]
    JSON_MERGE_PRESERVE('[1, 2]', '[2, 3]'), // [1, 2, 2, 3]
	JSON_MERGE_PRESERVE('{"x": 1, "z": 7}', '{"x": 2, "y": 3}'), // {"x": [1, 2], "y": 3, "z": 7}
	JSON_MERGE_PRESERVE('{"x": 1, "z": 7}', '{"x": 2, "z": null}'); // {"x": [1, 2], "z": [7, null]}

16. JSON_OVERLAPS determines whether there are the same keys or values

MySQL JSON_OVERLAPS()Functions detect whether two JSON documents have any of the same key-value pairs or array elements.

grammar:

JSON_OVERLAPS(json1, json2)

Parameter description:

  • json1: Required. A JSON document.

  • json2: Required. Another JSON document.

Return value:

  • The overlapping content of two JSON documents returns 1, otherwise 0.
  • The functions judgment rules are as follows:
    • When comparing two arrays, if the two arrays have at least one of the same elements, return1, otherwise return0。
    • When comparing two objects, if the two objects have at least one identical key-value pair, return1, otherwise return0。
    • When comparing two pure values, if the two values ​​are the same, return1, otherwise return0。
    • When comparing pure values ​​to arrays, if the value is a direct element in this array, return1, otherwise return0。
    • The result of comparing pure values ​​and objects is0。
    • The result of comparing arrays and objects is0。
  • returnNULL situation:The parameters areNULL。
  • Error status:Any parameter is not a valid JSON document.

Example:

SELECT 
	JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]'),	    // 1
	JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]'),	    // 0
    JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}'),	// 1
    JSON_OVERLAPS('{"x": 1}', '{"y": 2}'),	        // 0
    JSON_OVERLAPS('[1, 2, 3]', '3'),	            // 1
    JSON_OVERLAPS('[1, 2, [3]]', '3');	            // 0

17. JSON_PRETTY format output

MySQL JSON_PRETTY()Function formats output a JSON document for easier reading.

grammar:

JSON_PRETTY(json)

Parameter description:

  • json: Required. A value of a JSON document or JSON type.

Return value:

  • Format output JSON documents, easy to read.

Example:

SELECT JSON_PRETTY('[1, 2, 3]');
[
  1,
  2,
  3
]

SELECT JSON_PRETTY('{"x": 1, "y": 2}');
{
  "x": 1,
  "y": 2
}

18. JSON_REMOVE Delete the specified data

MySQL JSON_REMOVE()The function deletes the data specified by the path from a JSON document and returns the modified JSON document.

grammar:

JSON_REMOVE(json, path[, path] ...)

Parameter description:

  • json: Required. A JSON document.

  • path: Required. A valid path expression that cannot contain*or**。

Return value:

  • Deleted JSON document.
  • returnNULL situation:The JSON document or path isNULL。
  • Error report;
    • jsonNot a valid JSON document.
    • pathNot a valid path expression or equal to$Or it contains*or**

Example:

SELECT 
	JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]'),	// [1, 2]
	JSON_REMOVE('{"x": 1, "y": 2}', '$.x');				// {"y": 2}

19. JSON_REPLACE Replace data

MySQL JSON_REPLACE()The function replaces existing data in a JSON document and returns a new JSON document.

grammar:

JSON_REPLACE(json, path, value[, path2, value2] ...)

Parameter description:

  • json: Required. Modified JSON document.

  • path: Required. A valid path expression that cannot contain*or**。

  • value: Required. New data.

Return value:

  • Replaced JSON documentation.
  • BackNULL situation:The JSON document or path isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression or contains*or**。

Example:

SET @arr = '[1, [2, 3]]';
SELECT 
	JSON_REPLACE(@arr, '$[0]', 0, '$[2]', 6),    // [0, [2, 3]]
	JSON_REPLACE(@arr, '$[0]', 0, '$[1]', 6);    // [0, 6]

SET @obj = '{"x": 1}';
SELECT 
    JSON_REPLACE(@obj, '$.x', 'true', '$.y', 'true');   // {"x": "true"}
	JSON_REPLACE(@obj, '$.x', '[1, 2]'),                // {"x": "[1, 2]"}
	JSON_REPLACE(@obj, '$.x', JSON_ARRAY(1,2));         // {"x": [1, 2]}

20. JSON_SCHEMA_VALID Verify JSON Documents

MySQL JSON_SCHEMA_VALID()The function verifies a JSON document based on the specified JSON pattern and returns1The table is validated or returned0It means that the verification fails.

grammar:

JSON_SCHEMA_VALID(schema, json_doc)

Parameter description:

  • schema: Required. A JSON pattern. It must be a valid JSON object.

  • json_doc: Required. Verified JSON document.

Return value:

  •  1: The JSON document has been verified.
  •  0: The JSON document has not passed the verification.
  • returnNULL situation:Any parameter isNULL。

Example:

SET @schema = '{
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -128,
          "maximum": 127
        },
        "y": {
          "type": "number",
          "minimum": -128,
          "maximum": 127
        }
      },
      "required": ["x", "y"]
    }';
	
SELECT 
	JSON_SCHEMA_VALID(@schema, '{"x": 1}'),        // 0
	JSON_SCHEMA_VALID(@schema, '{"x": 1, "y": 2}') // 1

21. JSON_SCHEMA_VALIDATION_REPORT Verify JSON document

MySQL JSON_SCHEMA_VALIDATION_REPORT()The function verifies a JSON document based on the specified JSON pattern and returns a verification report.

grammar:

JSON_SCHEMA_VALIDATION_REPORT(schema, json_doc)

Parameter description:

  • schema: Required. A JSON pattern. It must be a valid JSON object.

  • json_doc: Required. Verified JSON document.

Return value:

  • Report of verification results.
  • {"valid": true}: Verification passed.
  • {"valid": false, reason:'...'}: The verification fails, reason is the reason for failure.

Example:

SET @schema = '{
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -90,
          "maximum": 90
        },
        "y": {
          "type": "number",
          "minimum": -180,
          "maximum": 180
        }
      },
      "required": ["x", "y"]
    }';
	
SELECT 
	JSON_SCHEMA_VALIDATION_REPORT(@schema, '{"x": 1}'), // {"valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"}
	JSON_SCHEMA_VALIDATION_REPORT(@schema, '{"x": 1, "y": 2}') // {"valid": true}

22. JSON_SEARCH Search

MySQL JSON_SEARCH()The function returns the path of a given string in a JSON document.

grammar:

JSON_SEARCH(json, one_or_all, search_str)
JSON_SEARCH(json, one_or_all, search_str, escape_char)
JSON_SEARCH(json, one_or_all, search_str, escape_char, path)

Parameter description:

  • json: Required. A JSON document.

  • one_or_all: Required. Available values:'one', 'all'. The rules are as follows:

    • 'one'：Returns the first matching path.
    • 'all'：Returns all matching paths. All paths are wrapped in an array and returned.

  • search_str: Required. The string being searched for. You cansearch_strUsed in parameters%and_Wildcards, just like LIKE:

    • %Match any number of arbitrary characters.
    • _Match any character.

  • escape_char: Optional. ifsearch_strIncluded%and_, need to add transfer characters before them. The default is\。

  • path: Optional. Search can only be done under this path.

Return value:

  • A path string or an array of multiple paths.
  • returnNULL situation:
    • The specified string was not searched.
    • The specified one does not exist in the JSON documentpath。
    • Any parameter isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression.

Example:

SET @json = '{
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -90,
          "maximum": 90
        },
        "y": {
          "type": "number",
          "minimum": -180,
          "maximum": 180
        }
      },
      "required": ["x", "y"]
    }';
	
SELECT 
	JSON_SEARCH(@json, 'one','number'), // "$."
	JSON_SEARCH(@json, 'all','number')  // ["$.", "$."]

23. JSON_SET Insert or update data

The MySQL JSON_SET() function inserts or updates data in a JSON document and returns a new JSON document. It is equivalent to a combination of JSON_INSERT() and JSON_REPLACE().

grammar:

JSON_SET(json, path, value[, path2, value2] ...)

Parameter description:

  • json: Required. Modified JSON document.

  • path: Required. A valid path expression that cannot contain*or**。

  • value: Required. Data to be set.

Return value:

  • Insert or update data and return a new JSON document. The rules are as follows:
    • Path exists: Update.
    • No path exists: Add.
  • likevalueAs a string: insert directly.
  • returnNULL situation:The JSON document or path isNULL。
  • Error status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression or contains*or**。

Example:

SET @obj = '{"x": 1}';
SELECT 
	JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]'),    // {"x": "10", "y": "[1, 2]"}
	JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}'),  // {"x": "10", "y": "{\"z\": 2}"}
	JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON)); // {"x": "10", "y": [1, 2]}

24. JSON_STORAGE_FREE Free space

The MySQL JSON_STORAGE_FREE() function returns the space freed after a JSON column is updated by JSON_SET(), JSON_REPLACE(), or JSON_REMOVE().

grammar:

JSON_STORAGE_FREE(json)

Parameter description:

  • json: Required. A JSON document. It can be a JSON string, or a JSON column.

Return value:

  • A JSON column is freed after being updated by JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(). It can accept a JSON string, or a JSON column as an argument.
  • Return to 0 situation:
    • The parameter is a JSON string.
    • The column has not been updated or is not partially updated using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE().
    • The content became larger after the update。
  • Returns the space released after update: The column (column of the database table) is partially updated by JSON_SET(), JSON_REPLACE(), or JSON_REMOVE().
  • returnNULL situation:The parameters areNULL。
  • Error report: jsonNot a valid JSON document.

Example:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    json_col JSON NOT NULL
);

INSERT INTO test
VALUES ('{"x": 1, "y": "99"}');

SELECT
    json_col,                    // {"x": 1, "y": "99"}
    JSON_STORAGE_SIZE(json_col), // 24
    JSON_STORAGE_FREE(json_col)  // 0
FROM
    test;
// {"x": 1, "y": "99"} ｜ 24  ｜  0


UPDATE test
SET json_col = JSON_REMOVE(json_col, '$.y');
SELECT
    json_col,                    // {"x": 1}
    JSON_STORAGE_SIZE(json_col), // 24
    JSON_STORAGE_FREE(json_col)  // 11
FROM
    test;

25. JSON_STORAGE_SIZE The number of bytes occupied

MySQL JSON_STORAGE_SIZE()The function returns the number of bytes occupied by a binary representation of a JSON document.

grammar:

JSON_STORAGE_SIZE(json)

Parameter description:

  • json: Required. A JSON document. It can be a JSON string, or a JSON column.

Return value:

  • The binary representation of the JSON document is the number of bytes occupied.
  • returnNULL：The parameters areNULL。
  • Error status:jsonNot a valid JSON document.

Example:

SELECT
    JSON_STORAGE_SIZE('100'),  // 3
    JSON_STORAGE_SIZE('"a"'),  // 3
	JSON_STORAGE_SIZE('true'), // 2
	JSON_STORAGE_SIZE('null'); // 2

26. JSON_TABLE Extracts Data

MySQL JSON_TABLE()The function extracts data from a specified JSON document and returns a relational table with the specified column.

grammar:

JSON_TABLE(
    json,
    path COLUMNS (column[, column[, ...]])
)

column:
    name FOR ORDINALITY
    |  name type PATH string_path [on_empty] [on_error]
    |  name type EXISTS PATH string_path
    |  NESTED [PATH] path COLUMNS (column[, column[, ...]])

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

Parameter description:

  • json: Required. A JSON document.

  • path: Required. A path expression.

  • column：Required. Define a column. You can define columns in the following 4 ways:

    • name FOR ORDINALITY: Generate a counter column starting from 1 with the namename。
    • name type PATH string_path [on_empty] [on_error]: Will be expressed by the pathstring_pathThe specified value is placed asnamein the column.
    • name type EXISTS PATH string_path:according tostring_pathWhether the specified position has a value will1or0Put the name asnamein the column.
    • NESTED [PATH] path COLUMNS (column[, column[, ...]]): Pull the data in the embedded object or array flattened in a row.
  • on_empty: optional. If specified, it determines the return value when there is no data in the specified path:
    • NULL ON EMPTY: If there is no data in the specified path, the JSON_TABLE() function will use NULL, which is the default behavior.
    • DEFAULT value ON EMPTY: If there is no data in the specified path, the JSON_TABLE() function will use value.
    • ERROR ON EMPTY: If there is no data in the specified path, the JSON_TABLE() function will throw an error.
  • on_error: optional. If specified, it determines the logic for handling errors:
    • NULL ON ERROR: If there is an error, the JSON_TABLE() function will use NULL, which is the default behavior.
    • DEFAULT value ON ERROR: If there is an error, the JSON_TABLE() function will use value.
    • ERROR ON ERROR: If there is an error, the JSON_TABLE() function will throw an error.

Return value:

  • Relationship table with specified columns.

Example:

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]',
        '$[*]'
        COLUMNS (
            id FOR ORDINALITY,
            x INT PATH '$.x' DEFAULT '100' ON EMPTY,
            y INT PATH '$.y'
        )
    ) AS t;


   id      x       y
+------+-------+--------+
|     1|     10|      11|
+-----------------------+
|     2|    100|      21|
+------+-------+--------+
|     2|     30|    NULL|
+------+-------+--------+

Example description:

  • Path expression $[*]: Each element in the array, that is, the two objects in the array.
    • $[0]: Extract only the 1st element in the JSON array
    • $[1]: Extract only the 2nd element in the JSON array
    • And so on
  • The COLUMNS clause defines 3 columns in the relationship table:
    • id FOR ORDINALITY: The column is named id, and the content of the column is an auto-incremental sequence starting from 1.
    • x INT PATH '$.x' DEFAULT '100' ON EMPTY: Use the default value 100 when the member x does not exist in the object or the value of x is empty.
    • y INT PATH '$.y: The column is named y, and the content of the column corresponds to the member y in the object.
  • where $.x and $.y represents the current context object, that is, each object in the array.

Example of flattening embedded arrays:

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":[11, 12]}, {"x":20,"y":[21, 22]}]',
        '$[*]'
        COLUMNS (
            x INT PATH '$.x',
            NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')
        )
    ) AS t;

    x     y    
+------+-------+
|   10 |   11  |
|   10 |   12  |
|   20 |   21  |
|   20 |   22  |
+------+-------+

Example description:

  • NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$'): ExpandyThe corresponding array andyEach element in the array is put into the nameyin the column.

Flatten the embedded objects:

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":{"a":11,"b":12}},{"x":20,"y":{"a":21,"b":22}}]',
        '$[*]'
        COLUMNS (
            x INT PATH '$.x',
            NESTED PATH '$.y' COLUMNS (
                ya INT PATH '$.a',
                yb INT PATH '$.b'
            )
        )
    ) AS t;

    x       ya      yb   
+------+-------+-------+
|   10 |    11 |    12 |
|   20 |    21 |    22 |
+------+-------+-------+

Example description:

  • NESTED PATH '$.y'The clause will objectyMembers in  extract 2 columns:
    • MemberaExtracted to columnya。
    • MemberbExtracted to columnyb。

27. JSON_TYPE gets the type

MySQL JSON_TYPE()The function returns the type of a given JSON value.

grammar:

JSON_TYPE(json_value)

Parameter description:

  • json_value: Required. A JSON value.

Return value: (oneutf8mb4String)

  • OBJECT: JSON object.
  • ARRAY: JSON array
  • BOOLEAN: JSON boolean
  • NULL: JSON nullValue
  • INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT,INTandBIGINTValue of type
  • DOUBLE: MySQL DOUBLEandFLOATValue of type
  • DECIMAL: MySQL DECIMALandNUMERICValue of type
  • DATETIME: MySQL DATETIMEandTIMESTAMPValue of type
  • DATE: MySQL DATEValue of type
  • TIME: MySQL TIMEValue of type
  • STRING: MySQL CHAR, VARCHAR, TEXT, ENUMandSET
  • BLOB: MySQL BINARY, VARBINARY, BLOBandBIT
  • OPAQUE: Types other than the above

Example:

SELECT 
    JSON_TYPE('true'),	// BOOLEAN
    JSON_TYPE('null'),	// NULL
    JSON_TYPE('"abc"');	// STRING

28. JSON_VALID verification is proficient

MySQL JSON_VALID()The function returns 0 and 1 to indicate whether the given parameter is a valid JSON document.

grammar:

JSON_VALID(str)

Parameter description:

  • str: Required. Content that needs to be verified.

Return value:

  • 1: It is a JSON document.
  • 0: Not a JSON document.
  • returnNULL situation:The parameters areNULL。

Example:

SELECT 
    JSON_VALID(1), 			  // 0
	JSON_VALID('1'),          // 1   
    JSON_VALID(true),         // 0
	JSON_VALID('true'),       // 1
    JSON_VALID('abc'),        // 0
	JSON_VALID('"abc"'),      // 1
	JSON_VALID('{"a": 1}'),   // 1
	JSON_VALID('{a: 1}');     // 0

29. JSON_VALUE extracts the value of the specified path

MySQL JSON_VALUE()The function extracts the value of the specified path from a specified JSON document and returns it.

grammar:

JSON_VALUE(json, path [RETURNING type] [{NULL | ERROR | DEFAULT value} ON EMPTY] [{NULL | ERROR | DEFAULT value} ON ERROR])

Parameter description:

  • json: Required. A JSON document.

  • path: Required. A path expression.

  • RETURNING type

    Optional. He decides the type of return value. You can use one of the following values:

    • FLOAT
    • DOUBLE
    • DECIMAL
    • SIGNED
    • UNSIGNED
    • DATE
    • TIME
    • DATETIME
    • YEAR (MySQL 8.0.22 and later)
    • CHAR
    • JSON
  • {NULL | ERROR | DEFAULT value} ON EMPTY：Optional. If specified, it determines the return value without data in the specified path:
    • NULL ON EMPTY: If there is no data in the specified path,JSON_VALUE()The function will returnNULL, this is the default behavior.
    • DEFAULT value ON EMPTY: If there is no data in the specified path,JSON_VALUE()The function will returnvalue。
    • ERROR ON EMPTY: If there is no data in the specified path,JSON_VALUE()The function will throw an error.
  • {NULL | ERROR | DEFAULT value} ON ERROR：Optional. If specified, it determines the logic for handling errors:
    • NULL ON ERROR: If there is an error,JSON_VALUE()The function will returnNULL, this is the default behavior.
    • DEFAULT value ON ERROR: If there is an error,JSON_VALUE()The function will returnvalue。
    • ERROR ON ERROR: If there is an error,JSON_VALUE()The function will throw an error.

Return value:

  • Data on the path.
  • Error report status:
    • jsonNot a valid JSON document.
    • pathNot a valid path expression.

Example:

SET @json = '[
  {
    "name": "cxian",
    "age": 22,
  },
  {
    "name": "jie",
    "age": 23  }
]';

SELECT 
    JSON_VALUE(@json, '$[0].age'), // 22
    JSON_VALUE(@json, '$[1].age'); // 23
    JSON_VALUE(@json, '$[1].age' RETURNING DECIMAL(9,2)), // 23.00
    JSON_VALUE(@json, '$[0].note' DEFAULT 'Nothing' ON EMPTY) // Nothing

30. Is MEMBER OF an element of an array?

MySQL MEMBER OF()Function checks whether a specified value is an element in a JSON array.

grammar:

value MEMBER OF(value, json_array)

Parameter description:

  • value: Required. A value. It can be of any type.

  • json_array: Required. A JSON array.

Return value:

  • 1: json_array contains value, or json_array is a value and is equal to the value.
  • 0: The array does not contain value.
  • Error status:json_arrayNot a valid JSON document.

Example:

SELECT
    1 MEMBER OF('[1, 2, "a"]'),               // 1
    'a' MEMBER OF('"a"'),                     // 1
    CAST('true' AS JSON) MEMBER OF('true')    // 1

31. JSON_QUOTE is wrapped into JSON string

MySQL JSON_QUOTE()The function wraps a value in double quotes to make it a JSON string value.

grammar:

JSON_QUOTE(str)

Parameter description:

  • str: Required. A string.

Return value:

  • A JSON string value surrounded by double quotes.
  • returnNULL situation:The parameters areNULL。
  • Special characters will be escaped using backslashes:
    • \": double quotes "
    • \b: Backspace character
    • \f: Page changer
    • \n: Line break
    • \r: Carriage return
    • \t: Tab characters
    • \\: Backslash \
    • \uXXXX: UTF-8 bytes of Unicode value XXXX

Example:

SELECT
    JSON_QUOTE('1'),       // "1"
    JSON_QUOTE('NULL'),    // "NULL"
    JSON_QUOTE('"NULL"')   // "\"NULL\""

Summarize

This is the article about easily getting started with MYSQL's JSON function to achieve efficient data query and operation. For more related MYSQL JSON function data query and operation content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!