MySQL8. 0 learning record 07 - JSON of data type

How to create JSON type ?

String representation

stay MySQL You can use strings to represent JSON type , legal JSON The string can be automatically parsed into JSON object . have access to JSON_VALID() To verify the legality , use JSON_TYPE To determine the type of object .

SET @j = '["abc", 10, null, true, false]';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1, ARRAY

SET @j = '{"k1": "value", "k2": 10}';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1, OBJECT

select JSON_VALID('"k2": 10')
-- 0

SET @j = '10';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1,INTEGER

SET @j = '"STR"';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1,STRING

select JSON_VALID('STR')
-- 0

Be careful STRING The value of type must be in double quotation marks , If the value of character type also contains double quotation marks , You can escape or use JSON_QUOTE() function ：

select '"Hello \\"Tom\\""', JSON_QUOTE('Hello "Tom"')
-- "Hello \"Tom\"", "Hello \"Tom\"" 

JSON_OBJECT([key, val[, key, val] …])

select JSON_OBJECT('name','test','age',12)
-- {"age": 12, "name": "test"}

SELECT JSON_OBJECT();
-- {}

Be careful JSON Object's Key Must be a string , If JSON_OBJECT Provides a non string key, Will turn into STRING:

select JSON_OBJECT('name','test',45,12)
-- {"45": 12, "name": "test"}

select JSON_OBJECT("name","test",now(),12);
-- {"name": "test", "2022-04-19 13:24:23": 12}

select  JSON_OBJECT("name","test",JSON_OBJECT('address','shanghai'),12);
-- {"name": "test", "{\"address\": \"shanghai\"}": 12}

JSON_ARRAY([val[, val] …])

SELECT JSON_ARRAY(1, 'abc','"ASD"',NULL, TRUE,FALSE, CURTIME());
-- [1, "abc", "\"ASD\"", null, true, false, "13:31:14.000000"]

SELECT JSON_ARRAY();
-- []

JSON_QUOTE(string)

Used to obtain STRING Type value

select JSON_QUOTE('[1, 2, 3]'),JSON_QUOTE('hi');
-- "[1, 2, 3]", "hi" 

CAST(value AS JSON)

select CAST('12' AS JSON),CAST('"12"' AS JSON),CAST(now() as JSON)
-- 12,"12","2022-04-19 14:00:56.000000"

Aggregate functions JSON_ARRAYAGG and JSON_OBJECTAGG

select JSON_ARRAYAGG('123'),JSON_OBJECTAGG('name','sdf')
-- ["123"],{"name": "sdf"} 

JSON_ARRAYAGG and JSON_OBJECTAGG The more powerful function is reflected in aggregating a set of table data , Take the example of the official website ：

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
     > FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {
   "color": "red", "fabric": "silk"}    |
|    3 | {
   "color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

How to access the JSON type ?

Inquire about JSON Type , Will turn into utf8mb4 Character set （ Proofread rules utf8mb4_bin） String

set @j = JSON_OBJECT('name','test','age',12);
select @j, CHARSET(@j), COLLATION(@j);
-- {"age": 12, "name": "test"},utf8mb4,utf8mb4_bin

JSON_EXTRACT

have access to -> Operation or ->> Operation to access ,->> Than -> More than a step JSON_UNQUOTE

create table t_json(
	jdoc json
);
insert into t_json(jdoc) values(JSON_OBJECT('name','hello'));
select jdoc -> '$.name',jdoc->>'$.name' from t_json ;
-- "hello",hello 

-> Equivalent to JSON_EXTRACT and ->> Equivalent to JSON_UNQUOTE(JSON_EXTRACT()), But functions can be used to get multiple values ：

set @j = JSON_OBJECT('name','test','age',12);
select JSON_EXTRACT(@j,'$.name') from t_json;
-- "test"
select JSON_EXTRACT(@j,'$.name','$.age') from t_json;
-- ["test", 12]

set @j = JSON_ARRAY('12',12,'SDF');
select JSON_EXTRACT(@j,'$[0]') from t_json;
-- "12"
select JSON_EXTRACT(@j,'$[1]','$[0]') from t_json;
-- [12, "12"]

For arrays , There is an interesting attribute last, Index of the last element ：

set @j = JSON_ARRAY('12',12,'SDF');
select JSON_EXTRACT(@j,'$[last]') from t_json;
-- "SDF"
select JSON_EXTRACT(@j,'$[last-1]','$[last-2]') from t_json;
-- [12, "12"]

JSON_VALUE

And JSON_EXTRACT The difference is ,JSON_EXTRACT Is it a value or a JSON Type but JSON_VALUE It also converts the value to the corresponding database type ：

set @j = JSON_ARRAY('12',12,'SDF');
select JSON_VALUE(@j,'$[last]'),JSON_EXTRACT(@j,'$[last]') from t_json;
-- SDF,"SDF"
select JSON_VALUE(@j,'$[0]'),JSON_EXTRACT(@j,'$[0]') from t_json;
-- 12,"12"
select JSON_VALUE(@j,'$[1]'),JSON_EXTRACT(@j,'$[1]') from t_json;
-- 12,12

JSON_SEARCH

MySQL Provides a number of functions to access JSON type ,JSON_SEARCH It is also very important , Can be used to get access path . He has two modes one Get the first matching path ,all Get all matching paths ：

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'one', 'abc');
-- "$[0]"
SELECT JSON_SEARCH(@j, 'all', 'abc');
-- ["$[0]", "$[2].x"]

You can also limit the search scope ：

SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
-- "$[2].x"

You can also fuzzy match ：

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'all', '%bc%');
-- ["$[0]", "$[2].x", "$[3].y"]
SELECT JSON_SEARCH(@j, 'all', '%bc_');
-- "$[3].y"

How to update JSON Type value ？

JSON_SET()

Modify existing or add new ：

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]]

We need to pay attention to the abnormal situation , For example, it doesn't exist c This array element , The following array index 4 non-existent ：

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].c[0]', 1, '$[2][4]', 2);
-- ["a", {"b": [true, false]}, [10, 20, 2]]
--  You can see c[0] No updates , Array elements 2 Added to the end 

But you can add new key, You can also add array elements , such as ：

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].c', 'hello', '$[1].d', JSON_ARRAY(3));
-- ["a", {"b": [true, false], "c": "hello", "d": [3]}, [10, 20]]

JSON_INSERT()

Only new values will be added , Existing elements will not be modified ：

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

JSON_REPLACE()

Only existing elements will be modified , Ignore new values or elements ：

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

JSON_REMOVE()

SET @j = '["a", {"b": [1, 2,3]}, [10, 20]]';
SELECT JSON_REMOVE(@j, '$[2][3]', '$[1].b[0]', '$[1].b[0]');
-- ["a", {"b": [3]}, [10, 20]]

Note here b The element in has been removed by two

About JSON Type of in-place to update

JSON Some elements of type update , The following conditions are met , The optimizer can use in-place to update , Instead of deleting the old file and writing the new file completely ：

• The column is JSON
• UPDATE Statements use JSON_SET()、 JSON_REPLACE() or JSON_REMOVE() updated
• The modified target column is the same as the input column of the function
• Must be to modify an existing value or element , Can't add new
• The size of the new value cannot exceed the old value

How to JSON Type use index ?

JSON Column , Like other binary type columns , You can't index directly .
But it can go through Generated Column Yes JSON Index the elements in ：

CREATE TABLE t_json (
	jdoc JSON,
	g varchar(30) GENERATED ALWAYS AS (jdoc->>"$.name"),
 	INDEX i (g)
);

insert into t_json(jdoc)
values('{"name":"abc","age":1}'),
('{"name":"bcd","age":2}'),
('{"name":"def","age":3}'),
('{"name":"efg","age":4}'),
('{"name":"hij","age":5}');

SELECT * FROM t_json where g = 'abc';

Query can use index . Here we need to pay attention to the operation of extracting values , Strings are best used ->>, instead of ->.