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

XHTML introductory tutorial: Web page Head and DTD

Although head and DTD will not be displayed on th...

N ways to center elements with CSS

Table of contents Preface Centering inline elemen...

HTTP Status Codes

This status code provides information about the s...

Solution to MySQL connection exception and error 10061

MySQL is a relational database management system ...

A brief discussion on the semantics of HTML and some simple optimizations

1. What is semanticization? Explanation of Bing D...

Detailed explanation of screen command usage in Linux

GUN Screen: Official website: http://www.gnu.org/...

Implementation steps for setting up the React+Ant Design development environment

Basics 1. Use scaffolding to create a project and...

How to export mysql query results to csv

To export MySQL query results to csv , you usuall...

Detailed explanation of how MySQL solves phantom reads

1. What is phantom reading? In a transaction, aft...

How to install Jenkins using Docker

Table of contents 1. Pull the image 2. Create a l...

How to install and configure MySQL and change the root password

1. Installation apt-get install mysql-server requ...

Detailed explanation of the working principle and usage of the Docker image submission command commit

After creating a container locally, you can creat...

Complete steps to configure IP address in Ubuntu 18.04 LTS

Preface The method of configuring IP addresses in...

Why MySQL does not recommend using null columns with default values

The answer you often hear is that using a NULL va...

Detailed explanation of for loop and double for loop in JavaScript

for loop The for loop loops through the elements ...