Basic JSON operations for MySQL5.7 (code example)

This article brings you the basic JSON operations (code examples) of MySQL5.7. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

JSON basic operations of MySQL5.7

MySQL has supported JSON format data since version 5.7, and the operation is very convenient.

Creating a table
When creating a new table, the field type can be directly set to the json type. For example, if we create a table:

mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);

json type field can be NULL

Insert data:

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');

The field of json type must be a valid json string

You can use the JSON_OBJECT() function to construct a json object:

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));

Use the JSON_ARRAY() function Construct a json array:

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

Now view the data in the test_user table:

mysql> select * from test_user; 
+----+-----------+--------------------------------------------+ 
| id | name      | info                                       |
+----+-----------+--------------------------------------------+ 
|  1 | xiaoming  | {"age": 18, "sex": 1, "nick_name": "小萌"} | 
|  2 | xiaohua   | {"age": 17, "sex": 0}                      |
|  3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]}   | 
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)

Query
Expression: The object is a json column->'$.key ', the array is json column->'$.key[index]'

mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user; 
+-----------+---------------------+---------------+------------------+ 
| name      | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' | 
+-----------+---------------------+---------------+------------------+ 
| xiaoming  | "小萌"              | 1             | NULL             | 
| xiaohua   | NULL                | 0             | NULL             | 
| xiaozhang | NULL                | 1             | 3                | 
+-----------+---------------------+---------------+------------------+ 
3 rows in set (0.04 sec)

is equivalent to: the object is JSON_EXTRACT(json column, '$.key'), the array is JSON_EXTRACT(json column, ' $.key[index]')

mysql> select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]')  from test_user;
 +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| name      | JSON_EXTRACT(info, '$.nick_name') | JSON_EXTRACT(info, '$.sex') | JSON_EXTRACT(info, '$.tag[0]') 
| +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| xiaoming  | "小萌"                            | 1                           | NULL                           |
| xiaohua   | NULL                              | 0                           | NULL                           | 
| xiaozhang | NULL                              | 1                           | 3                              | 
+-----------+-----------------------------------+-----------------------------+--------------------------------+ 
3 rows in set (0.04 sec)

However, I saw that "Xiaomeng" above has double quotes. This is not what we want. We can use the JSON_UNQUOTE function to remove the double quotes

mysql> select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming'; 
+----------+-----------------------------------+ 
| name     | JSON_UNQUOTE(info->'$.nick_name') | 
+----------+-----------------------------------+ 
| xiaoming | 小萌                              | 
+----------+-----------------------------------+ 
1 row in set (0.05 sec)

You can also use the operator directly ->>

mysql> select name, info->>'$.nick_name' from test_user where name='xiaoming';
+----------+----------------------+ 
| name     | info->>'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.06 sec)

Of course attributes can also be used as query conditions

mysql> select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌'; 
+----------+----------------------+ 
| name     | info->>'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.05 sec)

It is worth mentioning that you can use virtual columns to Quickly query specified attributes of JSON type.

Create virtual columns:

mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;

Note that using the operator ->>

is the same as the normal column query:

mysql> select name,nick_name from test_user where nick_name='小萌'; 
+----------+-----------+ 
| name     | nick_name | 
+----------+-----------+ 
| xiaoming | 小萌      | 
+----------+-----------+ 
1 row in set (0.05 sec)

Update
Use JSON_INSERT() to insert new values, but will not overwrite existing values

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;

Look at the results

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

Use JSON_SET() to insert new values ​​and overwrite existing values

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;

Look at the results

mysql> select * from test_user where id=3; 
+----+-----------+---------------------------------------------------------------+-----------+ 
| id | name      | info                                                          | nick_name | 
+----+-----------+---------------------------------------------------------------+-----------+ 
|  3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90], "nick_name": "小张"} | 小张      | 
+----+-----------+---------------------------------------------------------------+-----------+ 
1 row in set (0.06 sec)

Use JSON_REPLACE() to only replace existing values Value

mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;

Look at the result

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

You can see that the tag is not updated

Delete

Use JSON_REMOVE() to delete JSON elements

mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;

Look at the results

mysql> select * from test_user where id=1; 
+----+----------+----------------------------------+-----------+ 
| id | name     | info                             | nick_name | 
+----+----------+----------------------------------+-----------+ 
|  1 | xiaoming | {"age": 18, "nick_name": "小萌"} | 小萌      | 
+----+----------+----------------------------------+-----------+ 
1 row in set (0.05 sec)

This article is all over here. For more other exciting content, you can follow PHP Chinese Net's MySQL tutorial video column!

The above is the detailed content of Basic JSON operations for MySQL5.7 (code example). For more information, please follow other related articles on the PHP Chinese website!