Details of json_extract function in MySql processing json field

Preface

I believe everyone has used the data in JSON format in db. So is there any good way to query the data in this json structure? After taking out the String, parsing it in the code?

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

1. How to use

The data stored in the data 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, it can be passed$.field nameTo represent the corresponding value of the query

2. Use 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": ""}');

Query the json stringname，as follows:

mysql&gt; 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, the return is null instead of throwing an exception

mysql&gt; select json_extract(`val`, '$.name') as `name`, json_extract(`val`, '$.site') as `site` from `json_table`;
+-----------------+-------------------------+
| name            | site                    |
+-----------------+-------------------------+
| "A gray blog"    | NULL                    |
| "A gray blog"    | "" |
+-----------------+-------------------------+

Next, let’s take a look at how to organize it if it is a json array

mysql&gt; insert into `json_table` values (3, '[{"name": "A gray", "site": ""}]');
mysql&gt; select json_extract(`val`, '$[0].name') from `json_table` where id = 3;
+----------------------------------+
| json_extract(`val`, '$[0].name') |
+----------------------------------+
| "A gray"                         |
+----------------------------------+

In addition to using json_extract in query results, you can also use it in query conditions

mysql&gt; select * from `json_table` where json_extract(`val`, '$.name') = 'A gray blog';
+----+------------------------------------------------------------+
| id | val                                                        |
+----+------------------------------------------------------------+
|  1 | {"age": 18, "name": "A gray blog"}                          |
|  2 | {"name": "A gray blog", "site": ""}    |
+----+------------------------------------------------------------+

3. Summary

How to use json_extract function, used to parse the scene where the value in the field is a json string

This is the end of this article about the details of the json_extract function in MySql processing json field. For more related content on MySql processing json, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!