MySQL 8.0 can now handle JSON

1. Brief Overview
2. JSON basic tools
3. JSON Path expression
4. Find and modify JSON

Preface:

After a long test, the whole system will be migrated to Mysql8.0 ; Mysql8.0 has added/optimized many related Json API operations for Json operations; I read the official documents. Although most JSON operations are completed at the application layer, some Mysql JSON syntax is easy to debug ; I selected the basic and valuable parts for future reference;

https://dev.mysql.com/doc/ref...
https://dev.mysql.com/doc/ref...

1. Brief Overview

null is not allowed; the Json format definition is similar to LONGBLOB or LONGTEXT ; its maximum length is controlled by max_allowed_packet ;
The function to view the space occupied by the JSON field is JS ON_STORAGE_SIZE（xxx） ;
In addition to common Json operations, it also supports GeoJSON (geometry-based geospatial data exchange format) related operations;
Support indexing for Json columns (combined with the new feature Mysql8.0 , function index );
An optional optimization that supports partial, in-place updates of Json Column has been added to MySql8.0 ; the functions that can be used are JSON_SET() , JSON_REPLACE() , JSON_REMOVE() ; there are some restrictions when using it, but it will have better performance;

2. JSON basic tools

//Use the JSON_ARRAY method to define a JSON array;
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME())
//Result: [1, "abc", null, true, "11:30:24.000000"]  

//JSON_OBJECT method defines JSON object SELECT JSON_OBJECT('id', 87, 'name', 'carrot')
//Result {"id": 87, "name": "carrot"}

//Array and object nesting scenarios;
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"k1": "value", "k2": [10, 20]}

//Date/time type definition ["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

 //JSON_QUOTE escapes the JSON object into a String, that is, escapes the internal symbols and wraps the whole object in double quotes;
JSON_QUOTE(' "null" ')
//Result "\"null\""

//Beautify and output the JSON content;
JSON_PRETTY()

//You can convert JSON/elements inside JSON into other data types;
//Convert the id element in JSON jdoc to unsigned int as follows;
[https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types] (https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types)
ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED);

The JSON merging operations JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() are rarely used in actual business;

-> --> operator, finds the value according to the key; the difference is that --> will remove the " and escape characters; its equivalent Function form is JSON_EXTRACT()

// {"mascot": "Our mascot is a dolphin named \"Sakila\"."}
mysql> SELECT col->"$.mascot" FROM qtest;
// Result: | "Our mascot is a dolphin named \"Sakila\"." |
SELECT sentence->>"$.mascot" FROM facts;
// Result: | Our mascot is a dolphin named "Sakila". |

3. JSON Path expression

The content between the double quotes above --> is the so-called JSON Path expression ;
This syntax is part of the ECMAScript specification, so front-end programmers should be particularly familiar with it;

Take the following JSON as an example:

 [3, {"a": [5, 6], "b": 10}, [99, 100]]
 $[0] = 3 ;
 $[1] = {"a": [5, 6], "b": 10};
 $[2] = [99, 100];

At the same time, $[1], $[2] are not scalars, further

$[1].a = [5,6]
$[1].a[1] = 6
$[1].b = 10;
$[2][0] = 99;

Further supported syntax features $[n to m]

 $[ 1 to 2] = [{"a": [5, 6], "b": 10}, [99, 100]]
 $[last-2 to last-1] = [3, {"a": [5, 6], "b": 10}]

To summarize;

a .* represents all members in object ;
b [*] represents all cells in array ;
c [prefix] ** suffix represents all paths starting with prefix and ending with suffix;

4. Find and modify JSON

//As above, it should be possible to use --> syntax instead;
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
//[1, 2, [3, 4, 5]]  
SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]')
//[3, 4, 5]
SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
//[1, 2]
SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
//[2, 3, 4]

//JSON_SET JSON_INSERT JSON_REPLACE JSON_REMOVE
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
//| ["a", {"b": [1, false]}, [10, 20, 2]]    

SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
//["a", {"b": [true, false]}, [10, 20, 2]]

JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2)
//["a", {"b": [1, false]}, [10, 20]]

SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
//["a", {"b": [true]}]

A common scenario JSON Table Functions is that the JSON data itself is a table structure;

JSON_TABLE(*expr*, *path* COLUMNS (*column_list*) [AS\] *alias*)

SELECT * FROM JSON_TABLE( '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
  -> "$[*]"
  -> COLUMNS(
  -> rowid FOR ORDINALITY,
  -> ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
  -> aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
  -> bx INT EXISTS PATH "$.b"
  -> )
  -> ) AS tt;

Comparison and Ordering of JSON Values

I don’t feel it’s worth it at the moment;

Aggregation of JSON Values

I don't think it's worth it at the moment; you can use the aggregate function by converting the return value to another type;

This is the end of this article about MySQL 8.0 can operate JSON. For more relevant MySQL 8.0 operation JSON content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: