After lengthy testing, the entire migration to Mysql8.0 is imminent; Mysql8.0 adds/optimizes a number of Json related APIS for Json manipulation.

I have read the official document. Although most JSON operations are completed at the application layer, I know some JSON syntax of Mysql, which is convenient for debugging. Select basic and valuable parts for future reference;

https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html
Copy the code

A simple overview

Null is not allowed; Json format definition is similar to LONGBLOB or LONGTEXT; Its maximum length is controlled by max_allowed_packet;

JSON_STORAGE_SIZE (XXX);

In addition to normal Json operations, additional support for GeoJSON (geospatial data interchange format based on geometric graphics) some related operations;

Json column index support (in conjunction with Mysql8.0 new feature, function index);

An optional optimization that supports partial, in-place updates to Json columns was added to MySql8.0; Functions that can be used are JSON_SET(), JSON_REPLACE(),JSON_REMOVE(); When used, there are some constraints, but there is more performance;

JSON basic tools;

// Use the JSON_ARRAY method to define the JSON array; SELECT JSON_ARRAY(1, "ABC ", NULL, TRUE, CURTIME()) [1, "ABC ", null, true, "11:30:24.000000"] SELECT JSON_OBJECT('id', 87, 'name', Results of 'have') / / {" id ": 87," name ":" have "} / / arrays and objects nested scene; [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 JSON object to String, To escape the internal character, enclose the whole with double quotation marks; JSON_QUOTE (' "null") / / results null \ "" / /" \ "will be JSON content beautification and output; JSON_PRETTY() // Can convert JSON/JSON internal elements to other data types; // Convert the id element in JSON jdoc to an unsigned int; [https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types] The ORDER BY (https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types) CAST (JSON_EXTRACT (jdoc, '$.id') AS UNSIGNED);Copy the code

The combined JSON operations JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() are unlikely to be used in real business.

-> –> operator; The difference is –> will remove the wrap “and escape symbols; Its equivalent Function form is JSON_EXTRACT()

// {"mascot": "Our mascot is a dolphin named \"Sakila\"."} mysql> SELECT col->"$.mascot" FROM qtest; / / the result: | "Our mascot is a dolphin named \" Sakila \ ", "| SELECT sentence - > >". $mascot "FROM facts; / / results: | Our mascot is a dolphin named "Sakila". |Copy the code

JSON Path expression The content in the quotes above is 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].Copy the code

At the same time, [2] is not a scalar, further

$[1] = $[1]; $[1] = $[1]; $[1] = $[1]; $[2] [0] = 99;Copy the code

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}]
Copy the code

To sum up; A. all members of an object; B [] represents all cells in array; C [prefix] ** suffix indicates all paths that start with prefix and end with suffix. In addition, MySQL series interview questions and answers are all sorted out, wechat search Internet architect, sent in the background: 2T, can be read online. Find and modify JSON

// As above, it should be replaced by --> syntax; 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]}]Copy the code

A common scenario is where 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 (- > the rowid 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;Copy the code

Comparison and Ordering of JSON Values Aggregation of JSON Values You can use the aggregate function by casting the return value to another type;