Basic JSON Operation Guide in MySQL 5.7

Basic JSON Operation Guide in MySQL 5.7

Preface

Because of project needs, the storage fields are stored in JSON format. In the project, the queried values ​​are converted into corresponding beans through Jackson for processing, which is not simple and convenient enough.

MySQL has supported data in JSON format since version 5.7, which makes it very convenient to operate.

Create a table

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

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

JSON type fields can be NULL

Insert data:

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

A field of type json must be a valid json string

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

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

Use JSON_ARRAY() function to 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: Object is json column -> '$.key', 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 | "Xiaoming" | 1 | NULL | 
| xiaohua | NULL | 0 | NULL | 
| xiaozhang | NULL | 1 | 3 | 
+-----------+---------------------+---------------+------------------+ 
3 rows in set (0.04 sec)

Equivalent to: JSON_EXTRACT(json列, '$.鍵') for objects JSON_EXTRACT(json列, '$.鍵[index]') for arrays

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 | "Xiaoming" | 1 | NULL |
| xiaohua | NULL | 0 | NULL | 
| xiaozhang | NULL | 1 | 3 | 
+-----------+----------------------------------+-----------------------------+--------------------------------+ 
3 rows in set (0.04 sec)

However, you can see that "小萌" is enclosed in double quotes, which is not what we want. You 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 | 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 | 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'='Xiao Meng'; 
+----------+----------------------+ 
| name | info->>'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | Xiaoming| 
+----------+----------------------+ 
1 row in set (0.05 sec)

It is worth mentioning that virtual columns can be used to quickly query specified attributes of the JSON type.

Create a virtual column:

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

Note the use of the operator ->>

The usage is the same as the normal column query:

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

renew

Use JSON_INSERT() to insert new values ​​without overwriting 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": "小花"} | xiaohua| 
+----+---------+--------------------------------------------+-----------+ 
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": "小张"} | xiaozhang| 
+----+-----------+---------------------------------------------------------------+-----------+ 
1 row in set (0.06 sec)

Use JSON_REPLACE() to replace only existing values

mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') 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": 1, "nick_name": "小花"} | xiaohua| 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

You can see that the tag has not been updated.

delete

Removing JSON Elements Using JSON_REMOVE()

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": "Xiao Meng"} | Xiao Meng | 
+----+----------+----------------------------------+-----------+ 
1 row in set (0.05 sec)

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • MySQL operations: JSON data type operations
  • Detailed explanation of JSON series operation functions in Mysql
  • Summary of related functions for Mysql query JSON results
  • MySQL json format data query operation

<<:  Introduction and usage of Angular pipeline PIPE

>>:  An example of how Tomcat manages Session

Recommend

jQuery implements form validation

Use jQuery to implement form validation, for your...

Detailed explanation of CocosCreator project structure mechanism

Table of contents 1. Project folder structure 1. ...

Using js to implement a number guessing game

Last week, the teacher gave me a small homework, ...

Detailed analysis of GUID display issues in Mongodb

Find the problem I recently migrated the storage ...

Solution to the problem of English letters not wrapping in Firefox

The layout of text has some formatting requiremen...

Example code for implementing timeline and animation effects using JavaScript (front-end componentization)

Table of contents Code cleaning "Frames"...

How to implement responsive layout in vue-cli

When we are doing front-end development, we will ...

Example of using negative margin to achieve average layout in CSS

For evenly distributed layouts, we generally use ...

Some notes on modifying the innodb_data_file_path parameter of MySQL

Preface innodb_data_file_path is used to specify ...

Installation and deployment of Linux tool Nethogs to monitor network bandwidth by process

Overview There are many open source network monit...

Solve the compatibility issue between MySQL 8.0 driver and Alibaba Druid version

This article mainly introduces the solution to th...

Solution to the problem that the div width is set to width:100% and then the padding or margin exceeds the parent element

Preface This article introduces how to use the ne...

Tutorial on resetting the root password of Mac MySQL

Disclaimer: This password reset method can direct...

Detailed troubleshooting of docker.service startup errors

Execute the following command to report an error ...

MySQL 5.7.20 installation and configuration method graphic tutorial (win10)

This article shares the installation and configur...