MySql's json_extract function processes json fields

MySql's json_extract function processes json fields

Storing data in json format in db, I believe everyone has used it more or less, so 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

1. How to use

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

Syntax:

JSON_EXTRACT(json_doc, path[, path] …)

If the json string is not an array, you can use $.field name to indicate the value corresponding to the query

2. Use the demo

Create a test table

CREATE TABLE `json_table` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key id', `val` json DEFAULT NULL COMMENT 'json string', PRIMARY KEY(`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert a few pieces of data

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

Query the name in the json string, as follows

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

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

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

Let's take a look at how to adjust if it is a json array

mysql> insert into `json_table` values ​​(3, '[{"name": "One gray gray", "site": "https://spring.hhui.top"}]');mysql> select json_extract(`val`, '$[0].name') from `json_table` where id = 3;+----------------------------------+| json_extract(`val`, '$[0].name') |+----------------------------------+| "A grayish gray" |+-------------------------------------+

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') = 'a gray blog';+----+------------------------------------------------------------+| id | val |+----+--------------------------------------------------------------------------+| 1 | {"age": 18, "name": "A gray blog"} || 2 | {"name": "Huihui blog", "site": "https://blog.hhui.top"} |+----+------------------------------------------------------------+

3. Summary

This article mainly introduces the usage 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 information

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 inevitably omissions and mistakes. If you find bugs or have better suggestions, criticisms and corrections are welcome, and I will be grateful

• Personal site: https://blog.hhui.top
• Weibo address: Xiaohuihui Blog
• QQ: A gray gray/3302797840
• WeChat Official Account: A Grey Blog