MySql's json_extract function processes json fields

MySql's json_extract function processes json fields

To store data in json format in db, I believe everyone has used it more or less. Is there any good way to query the data in this json structure? After taking out the String, do the parsing in the code?

Next, this article will introduce the json_extract function provided after Mysql5.7+, which can query the value value by key

<!-- more -->

1. How to use

The data stored in the data store is a json string, and the type is our commonly used varchar.

grammar:

 JSON_EXTRACT(json_doc, path[, path] …)

If the json string is not an array, you can use $.字段名 to indicate the corresponding value of the query

2. Use the demo

Create a test table

 CREATE TABLE `json_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `val` json DEFAULT NULL COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert some data

 insert into `json_table` values (1, '{"name": "一灰灰blog", "age": 18}');
insert into `json_table` values (2, '{"name": "一灰灰blog", "site": "https://blog.hhui.top"}');

Query name in the json string, as follows

 mysql> select json_extract(`val`, '$.name') from `json_table`;
+-------------------------------+
| json_extract(`val`, '$.name') |
+-------------------------------+
| "一灰灰blog"                  |
| "一灰灰blog"                  |
+-------------------------------+

If the query key is not in the json string, return null instead of throwing an exception

 mysql> select json_extract(`val`, '$.name') as `name`, json_extract(`val`, '$.site') as `site` from `json_table`;
+-----------------+-------------------------+
| name            | site                    |
+-----------------+-------------------------+
| "一灰灰blog"    | NULL                    |
| "一灰灰blog"    | "https://blog.hhui.top" |
+-----------------+-------------------------+

Next, let's take a look at how to adjust if it is a json array

 mysql> insert into `json_table` values (3, '[{"name": "一灰灰", "site": "https://spring.hhui.top"}]');


mysql> select json_extract(`val`, '$[0].name') from `json_table` where id = 3;
+----------------------------------+
| json_extract(`val`, '$[0].name') |
+----------------------------------+
| "一灰灰"                         |
+----------------------------------+

In addition to using json_extract in query results, it can also be used in query conditions

 mysql> select * from `json_table` where json_extract(`val`, '$.name') = '一灰灰blog';
+----+------------------------------------------------------------+
| id | val                                                        |
+----+------------------------------------------------------------+
|  1 | {"age": 18, "name": "一灰灰blog"}                          |
|  2 | {"name": "一灰灰blog", "site": "https://blog.hhui.top"}    |
+----+------------------------------------------------------------+

3. Summary

This article mainly introduces the usage posture of the json_extract function, which is used to parse the scene where the value in the field is a json string

basic posture

• JSON object: json_extract('db field', '$.json string key')
• JSON array: json_extract('db field', '$[array subscript].json string key')

A grey contact

It is better to have no books than no books. The above content is purely from one family. Due to limited personal ability, there are unavoidable omissions and mistakes. If you find bugs or have better suggestions, you are welcome to criticize and correct them. Thank you very much.

• Personal site: https://blog.hhui.top
• Weibo address: Xiaohuihui Blog
• QQ: A gray gray / 3302797840
• WeChat public account: a gray blog