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: