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

js implements form validation function

This article example shares the specific code of ...

Web Design: The Accurate Location and Use of Massive Materials

Three times of memorization allows you to remembe...

Example implementation of checking whether an object is empty in native javascript

Table of contents What is native JavaScript A. Ch...

Javascript common higher-order functions details

Table of contents 1. Common higher-order function...

Vue implements a simple shopping cart example

This article example shares the specific code of ...

Install Kafka in Linux

Table of contents 1.1 Java environment as a prere...

MySQL table and column comments summary

Just like code, you can add comments to tables an...

Zabbix3.4 method to monitor mongodb database status

Mongodb has a db.serverStatus() command, which ca...

Example of how to import nginx logs into elasticsearch

The nginx logs are collected by filebeat and pass...

html option disable select select disable option example

Copy code The code is as follows: <select> ...

Implementing a simple student information management system based on VUE

Table of contents 1. Main functions 2. Implementa...

Summary of MYSQL full backup, master-slave replication, cascading replication, and semi-synchronization

mysql full backup 1. Enable binary log and separa...

Docker-compose steps to configure the spring environment

Recently, I need to package the project for membe...

Detailed explanation of referential integrity in SQL (one-to-one, one-to-many, many-to-many)

1. Referential Integrity Referential integrity re...

How to solve the synchronization delay caused by MySQL DDL

Table of contents Preface Solution Tool Introduct...