Shulou(Shulou.com)06/03 Report--

This article is to share with you about whether MySQL 8.0 can operate JSON, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Foreword:

After a long period of testing, the overall migration to Mysql8.0; Mysql8.0 has added / optimized a lot of API operations related to Json for Json operations. After reading the official documents, although the vast majority of JSON operations are done at the application layer, there are some JSON syntax of Mysql, which is convenient for debug; to select the basic and valuable parts for future reference.

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

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

1. A brief overview

Definition of null; Json format is not allowed to be similar to LONGBLOB or LONGTEXT; its maximum length is controlled by max_allowed_packet

JSON_STORAGE_SIZE (xxx) when viewing the function of the space occupied by the JSON field

In addition to ordinary Json operations, some related operations are supported for GeoJSON (geospatial data exchange format based on geometry).

Support indexing for Json fields (combined with the new features of Mysql8.0, the function index)

An optional optimization item that can partially update Json Column is added to MySql8.0; the functions that can be used are JSON_SET (), JSON_REPLACE (), JSON_REMOVE (); when used, there are some constraints, but there will be more performance

2. JSON basic tools / / use JSON_ARRAY method to define JSON array SELECT JSON_ARRAY (1, "abc", NULL, TRUE, CURTIME ()) / / result: [1, "abc", null, true, "11 SELECT JSON_OBJECT 30 id', 24.000000"] / / JSON_OBJECT method defines the JSON object SELECT JSON_OBJECT ('id', 87,' name', 'carrot') / / result {"id": 87, "name": "carrot"} / / the array is nested with the object [99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"K1": "value", "K2": [10,20]} / / date / time type definition ["12 cold 1829.000000", "2015-07-29", "2015-07-29"] / JSON_QUOTE escapes JSON objects into String Is to escape the internal symbol. And enclose the whole package in double quotes JSON_QUOTE ('"null"') / / result "\" null\ "/ / beautify and output JSON content JSON_PRETTY () / / can convert the elements inside JSON/JSON to other data types; / / convert the id elements 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 operation of merging JSON JSON_MERGE_PRESERVE () and JSON_MERGE_PATCH () has little possibility of actual business use.

The-> operator, which looks for values according to key; the difference is that-> removes the wrapped "and escape symbols; 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 in the double quotation marks 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}, [99100]] $[0] = 3; $[1] = {"a": [5,6], "b": 10}; $[2] = [99100]

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

$[1] .a = [5jue 6] $[1] .a [1] = 6 $[1] .b = 10 X $[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 sum up.

A. * represents all members in object

B [*] represents all cells in array

C [prefix] * * suffix represents all paths that begin with prefix and end with suffix

4. Find and modify JSON// as above, you should be able to replace it with-- > 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_REMOVESET @ 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 in 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": [1djue 2]}]',-> "$[*]"-> COLUMNS (- > rowid FOR ORDINALITY,-> ac VARCHAR) 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 worthless at the moment.

Aggregation of JSON Values

At present, there is no sense of inverse value; if you convert the return value to another type, you can use the aggregate function.

The above is whether MySQL 8.0 can operate JSON, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.