How to operate json fields in MySQL

How to operate json fields in MySQL

MySQL 5.7.8 introduced the json field. This type of field is used less frequently, but in actual operations, some businesses still use it. Let's take this as an example to introduce how to operate the json field:

Let’s start with an example:

mysql> create table test1(id int,info json);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values ​​(1,'{"name":"yeyz","age":26}'),(2,'{"name":"zhangsan","age":30}'),(3,'{"name":"lisi","age":35}');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test1;
+------+---------------------------------+
| id | info |
+------+---------------------------------+
| 1 | {"age": 26, "name": "yeyz"} |
| 2 | {"age": 30, "name": "zhangsan"} |
| 3 | {"age": 35, "name": "lisi"} |
+------+---------------------------------+
3 rows in set (0.00 sec)

First, we created a table test1, where id is an int field and info is a json field, and inserted three pieces of data, as shown above:

mysql> select * from test1 where json_extract(info,"$.age")>=30;
+------+---------------------------------+
| id | info |
+------+---------------------------------+
| 2 | {"age": 30, "name": "zhangsan"} |
| 3 | {"age": 35, "name": "lisi"} |
+------+---------------------------------+
2 rows in set (0.00 sec)

We can get the content in json through the json_extract method. in:

1. The $ symbol represents the root directory of json.

2. We use $.age which is equivalent to taking out the age field in json.

3. Of course, at the beginning of the function, the field name info should be written

Let's look at the commonly used functions in json:

a. json_valid determines whether it is a json field. If so, it returns 1, if not, it returns 0

mysql> select json_valid(2);
+---------------+
| json_valid(2) |
+---------------+
| 0 |
+---------------+
1 row in set (0.01 sec)
mysql> select json_valid('{"num":2}');
+-------------------------+
| json_valid('{"num":2}') |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select json_valid('2');
+-----------------+
| json_valid('2') |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select json_valid('name');
+--------------------+
| json_valid('name') |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)

It should be noted here that if the string 2 is passed in, the return result is 1

b. json_keys returns the top-level key value of the json field.

mysql> select json_keys('{"name":"yeyz","score":100}');
+------------------------------------------+
| json_keys('{"name":"yeyz","score":100}') |
+------------------------------------------+
| ["name", "score"] |
+------------------------------------------+
1 row in set (0.01 sec)
mysql> select json_keys('{"name":"yeyz","score":{"math":100,"English":95}}');
+----------------------------------------------------------------+
| json_keys('{"name":"yeyz","score":{"math":100,"English":95}}') |
+----------------------------------------------------------------+
| ["name", "score"] |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
#If there are multiple layers, you can use the $ method at the end to get the directory of one of the layersmysql> select json_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score');
+--------------------------------------------------------------------------+
| json_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score') |
+--------------------------------------------------------------------------+
| ["math", "English"] |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

c. The json_length function returns the number of keys in the top layer. If you want to get a certain layer in the middle, you can use the $ method as follows:

mysql> select json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}');
+---------------------------------------------------------------------------+
| json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}') |
+---------------------------------------------------------------------------+
| 3 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score');
+-----------------------------------------------------------------------------------------------------+
| json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score') |
+-----------------------------------------------------------------------------------------------------+
| 2 |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

d. json_depth function, the depth of the json file, the test example is as follows:

mysql> select json_depth('{"aaa":1}'),json_depth('{}');
+-------------------------+------------------+
| json_depth('{"aaa":1}') | json_depth('{}') |
+-------------------------+------------------+
| 2 | 1 |
+-------------------------+------------------+
1 row in set (0.00 sec)

mysql> select json_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}');
+--------------------------------------------------------------------------+
| json_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}') |
+--------------------------------------------------------------------------+
| 3 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

It should be noted here that the depth of JSON in the form of {'aa':1} is 2

e. The json_contains_path function retrieves whether there is one or more members in json.

mysql> set @j='{"a":1,"b":2,"c":{"d":4}}';
Query OK, 0 rows affected (0.00 sec)
#one means that as long as it contains one member, it returns 1
mysql> select json_contains_path(@j,'one','$.a','$.e');
+------------------------------------------+
| json_contains_path(@j,'one','$.a','$.e') |
+------------------------------------------+
| 1 |
+------------------------------------------+
1 row in set (0.00 sec)
#all means that all members are included, and only then will 1 be returned
mysql> select json_contains_path(@j,'all','$.a','$.e');
+------------------------------------------+
| json_contains_path(@j,'all','$.a','$.e') |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row in set (0.01 sec)

mysql> select json_contains_path(@j,'one','$.c.d');
+--------------------------------------+
| json_contains_path(@j,'one','$.c.d') |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains_path(@j,'one','$.a.d');
+--------------------------------------+
| json_contains_path(@j,'one','$.a.d') |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)

f. The json_type function determines the type of members in json and needs to be used in conjunction with json_extract.

mysql> select * from test1;
+------+---------------------------------+
| id | info |
+------+---------------------------------+
| 1 | {"age": 26, "name": "yeyz"} |
| 2 | {"age": 30, "name": "zhangsan"} |
| 3 | {"age": 35, "name": "lisi"} |
+------+---------------------------------+
3 rows in set (0.00 sec)
#Judge the type of namemysql> select json_type(json_extract(info,"$.name")) from test1;
+----------------------------------------+
| json_type(json_extract(info,"$.name")) |
+----------------------------------------+
| STRING |
| STRING |
| STRING |
+----------------------------------------+
3 rows in set (0.00 sec)
#Determine the type of agemysql> select json_type(json_extract(info,"$.age")) from test1;
+---------------------------------------+
| json_type(json_extract(info,"$.age")) |
+---------------------------------------+
| INTEGER |
| INTEGER |
| INTEGER |
+---------------------------------------+
3 rows in set (0.00 sec)
#Judge the type of the combination of name and age, you can see that it is array
mysql> select json_type(json_extract(info,"$.name","$.age")) from test1;
+------------------------------------------------+
| json_type(json_extract(info,"$.name","$.age")) |
+------------------------------------------------+
| ARRAY |
| ARRAY |
| ARRAY |
+------------------------------------------------+
3 rows in set (0.00 sec)

g. The role of *, all values, see the examples below.

{
 "a":1,
 "b":2,
 "c":
   {
    "d":4
   }
 "e":
   {
   "d":
     {
     "ddd":
     "5"
     }
   }
}
mysql> set @j='{"a":1,"b":2,"c":{"d":4},"e":{"d":{"ddd":"5"}}}';
Query OK, 0 rows affected (0.00 sec)
#All membersmysql> select json_extract(@j,'$.*');
+---------------------------------------+
| json_extract(@j,'$.*') |
+---------------------------------------+
| [1, 2, {"d": 4}, {"d": {"ddd": "5"}}] |
+---------------------------------------+
1 row in set (0.00 sec)
#d member among all membersmysql> select json_extract(@j,'$.*.d');
+--------------------------+
| json_extract(@j,'$.*.d') |
+--------------------------+
| [4, {"ddd": "5"}] |
+--------------------------+
1 row in set (0.00 sec)

The above is the detailed content of the operation method of JSON field in MySQL. For more information about MySQL JSON field, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • A simple explanation of MySQL parallel replication
  • Troubleshooting the reasons why MySQL deleted records do not take effect
  • Differences between MySQL CHAR and VARCHAR when storing and reading
  • MySQL learning tutorial clustered index
  • A brief discussion on MySQL large table optimization solution
  • Descending Index in MySQL 8.0
  • Detailed explanation of storage engine in MySQL
  • A case study on MySQL optimization
  • How to skip errors in mysql master-slave replication
  • A brief analysis of MySQL parallel replication

<<:  Vue3 encapsulates its own paging component

>>:  Detailed tutorial for springcloud alibaba nacos linux configuration

Recommend

js to upload pictures to the server

This article example shares the specific code of ...

How to install MySQL via SSH on a CentOS VPS

Type yum install mysql-server Press Y to continue...

Detailed explanation of browser negotiation cache process based on nginx

This article mainly introduces the detailed proce...

How to install Graphviz and get started tutorial under Windows

Download and installConfigure environment variabl...

Mini Program implements custom multi-level single-select and multiple-select

This article shares the specific code for impleme...

Ubuntu installation graphics driver and cuda tutorial

Table of contents 1. Uninstall the original drive...

Detailed explanation of Linux zabbix agent deployment and configuration methods

1. Install zabbix-agent on web01 Deploy zabbix wa...

Solution to ONLY_FULL_GROUP_BY error in Mysql5.7 and above

Recently, during the development process, the MyS...

Example of using CSS to achieve semi-transparent background and opaque text

This article introduces an example of how to use ...

How to run Linux commands in the background

Normally, when you run a command in the terminal,...

How to use MySQL 5.7 temporary tablespace to avoid pitfalls

Introduction MySQL 5.7 aims to be the most secure...

CSS margin overlap and how to prevent it

The vertically adjacent edges of two or more bloc...

Introduction to user management under Linux system

Table of contents 1. The significance of users an...

Vue Element front-end application development conventional Element interface components

Table of contents 1. List interface and other mod...

Analysis of the underlying principle of MySQL multi-version concurrency control MVCC

Table of contents 1 Problems encountered in trans...