Comment utiliser json_extract dans MySQL

1. Avant-propos

La version mysql5.7 commence à prendre en charge les champs de type JSON
json_extract peut être complètement abrégé en ->
json_unquote(json_extract()) peut être complètement abrégé en ->&g t ;
La plupart des introductions suivantes utiliseront des abréviations

2. Créez un exemple de tableau

CREATE TABLE `test_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

# 插入两条测试用的记录
INSERT INTO `test_json` (`content`) VALUES ('{\"name\":\"tom\",\"age\":18,\"score\":[100,90,87],\"address\":{\"province\":\"湖南\",\"city\":\"长沙\"}}');
INSERT INTO `test_json` (`content`) VALUES ('[1, "apple", "red", {"age": 18, "name": "tom"}]');

id	content
1	{"age": 18 "name " : "tom", "score": [100, 90, 87], "adresse": {"ville": "Changsha", "province": "Hunan"}}
2	[1, "pomme ", "red ", {"age": 18, "name": "tom"}]

3. Syntaxe de base

- Récupérer la valeur correspondant à une clé dans l'objet JSON

• Dans la fonction json_extract, le premier contenu d'un paramètre représente les données json et le deuxième paramètre est le chemin json, où $ représente les données json elles-mêmes, $.name représente l'obtention de la valeur avec le nom de la clé dans json

• Vous pouvez utilisez l'expression -> Au lieu de json_extract

• Si la valeur obtenue est elle-même une chaîne, alors la valeur obtenue sera entourée de guillemets, tels que "tom". Lorsque ce type de données est analysé dans un objet programme, il peut être échappé en tant que "tom" . Afin de résoudre ce problème, vous pouvez envelopper une autre couche de fonction json_unquote à l'extérieur, ou utiliser ->> au lieu de ->

content:
{"age": 18, "name": "tom " , "score": [100, 90, 87], "address": {"city": "Changsha", "province": "Hunan"}}

# 得到"tom"
select json_extract(content,'$.name') from test_json where id = 1;
# 简写方式：字段名->表达式等价于json_extract(字段名,表达式)
select content->'$.name' from test_json where id = 1;
# 结果:
+--------------------------------+
| json_extract(content,'$.name') |
+--------------------------------+
| "tom"                          |
+--------------------------------+
+-------------------+
| content->'$.name' |
+-------------------+
| "tom"             |
+-------------------+

# 解除双引号，得到tom
select json_unquote(json_extract(content,'$.name')) from test_json where id = 1;
# 简写方式：字段名->>表达式等价于json_unquote(json_extract(字段名,表达式))
select content->>'$.name' from test_json where id = 1;
# 结果:
+----------------------------------------------+
| json_unquote(json_extract(content,'$.name')) |
+----------------------------------------------+
| tom                                          |
+----------------------------------------------+
+--------------------+
| content->>'$.name' |
+--------------------+
| tom                |
+--------------------+

- Obtenez un élément dans le tableau JSON

• Dans la fonction json_extract, le contenu du premier paramètre représente les données json, et le deuxième paramètre est le chemin json, où $ représente les données json elles-mêmes, et $[i] représente l'obtention de l'élément d'index i du tableau json ( l'index commence à 0)

• Comme pour obtenir key-val, si l'élément obtenu est une chaîne, la méthode par défaut obtiendra également les caractères entre guillemets doubles, provoquant l'échappement du programme. la fonction json_unquote, ou utilisez ->> Au lieu de ->

content:
[1, "apple", "red", {"age": 18, "name": "tom"} ]

# 得到"apple"
select json_extract(content,'$[1]') from test_json where id = 2;
# 简写，效果同上
select content->'$[1]' from test_json where id = 2;
# 结果:
+------------------------------+
| json_extract(content,'$[1]') |
+------------------------------+
| "apple"                      |
+------------------------------+
+-----------------+
| content->'$[1]' |
+-----------------+
| "apple"         |
+-----------------+

# 解除双引号，得到apple 
select json_unquote(json_extract(content,'$[1]')) from test_json where id = 2;
# 简写，效果同上
select content->>'$[1]' from test_json where id = 2;
# 结果:
+--------------------------------------------+
| json_unquote(json_extract(content,'$[1]')) |
+--------------------------------------------+
| apple                                      |
+--------------------------------------------+
+------------------+
| content->>'$[1]' |
+------------------+
| apple            |
+------------------+

- Obtenez des données imbriquées dans des données JSON

Combinées avec les deux méthodes d'acquisition présentées précédemment, vous pouvez obtenir les données imbriquées dans des données json

content: id=1
{"age": 18, "name" : "tom", "score": [ 100, 90, 87], "adresse": {"ville": "Changsha", "province": "Hunan"}}
contenu: id=2
[1, " apple", "red", {"age ": 18, "name": "tom"}]

# 得到:87
select content->'$.score[2]' from test_json where id = 1;
# 结果:
+-----------------------+
| content->'$.score[2]' |
+-----------------------+
| 87                    |
+-----------------------+

# 得到:18
select content->'$[3].age' from test_json where id = 2;
# 结果:
+---------------------+
| content->'$[3].age' |
+---------------------+
| 18                  |
+---------------------+

4. S'améliorer

- L'obtention de données à partir de plusieurs chemins JSON

combinera les données de plusieurs chemins en un tableau et renvoyez-le

content : id=1
{"age": 18, "name": "tom", "score": [100, 90, 87], "address": {"city": "Changsha", "province": " Hunan"}}

select json_extract(content,'$.age','$.score') from test_json where id = 1;
# 结果:
+-----------------------------------------+
| json_extract(content,'$.age','$.score') |
+-----------------------------------------+
| [18, [100, 90, 87]]                     |
+-----------------------------------------+

select json_extract(content,'$.name','$.address.province','$.address.city') from test_json where id = 1;
# 结果:
+----------------------------------------------------------------------+
| json_extract(content,'$.name','$.address.province','$.address.city') |
+----------------------------------------------------------------------+
| ["tom", "湖南", "长沙"]                                              |
+----------------------------------------------------------------------+

- L'utilisation de l'expression de chemin *

combinera les données de plusieurs chemins dans un tableau et les renverra

# 先插入一条用于测试的数据
INSERT INTO `test_json` (`id`,`content`) VALUES(3,'{"name":"tom","address":{"name":"中央公园","city":"长沙"},"class":{"id":3,"name":"一年三班"},"friend":[{"age":20,"name":"marry"},{"age":21,"name":"Bob"}]}')

content : id=3
{ "name": "tom", "class " : {"id": 3, "name": "trois cours par an"}, "friend": [{"age": 20, "name": "marier" }, {"age": 21, " name": "Bob"}], "address": {"city": "Changsha", "name": "Central Park"}}

# 获取所有二级嵌套中key=name的值
# 由于friend的二级嵌套是一个数组,所以.name获取不到其中的所有name值
select content->'$.*.name' from test_json where id = 3;
+----------------------------------+
| content->'$.*.name'              |
+----------------------------------+
| ["一年三班", "中央公园"]         |
+----------------------------------+```

# 获取所有key为name值的数据，包括任何嵌套内的name
select content->'$**.name' from test_json where id = 3;
+---------------------------------------------------------+
| content->'$**.name'                                     |
+---------------------------------------------------------+
| ["tom", "一年三班", "marry", "Bob", "中央公园"]         |
+---------------------------------------------------------+

# 获取数组中所有的name值
select content->'$.friend[*].name' from test_json where id = 3;
+-----------------------------+
| content->'$.friend[*].name' |
+-----------------------------+
| ["marry", "Bob"]            |
+-----------------------------+

- Renvoie la valeur NULL

contenu : id=1
{ "âge": 18, "nom": "tom", "score": [100, 90, 87], "adresse": {"ville": "Changsha", " province » : « Hunan »}}

Aucun des chemins JSON recherchés n'existe

# age路径不存在，返回NULL
# 若有多个路径，只要有一个路径存在则不会返回NULL
select json_extract(content,'$.price') from test_json where id = 1;
+---------------------------------+
| json_extract(content,'$.price') |
+---------------------------------+
| NULL                            |
+---------------------------------+

Il y a NULL dans le chemin

# 存在任意路径为NULL则返回NULL
select json_extract(content,'$.age',NULL) from test_json where id = 1;
+------------------------------------+
| json_extract(content,'$.age',NULL) |
+------------------------------------+
| NULL                               |
+------------------------------------+

- Renvoie une erreur

Si le premier paramètre n'est pas une donnée de type JSON, une erreur sera renvoyé

select json_extract('{1,2]',$[0])

Si l'expression du chemin n'est pas standardisée, une erreur sera renvoyée

select content->'$age' from test_json where id = 1;
# 结果:
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 1.

5 Scénarios d'utilisation

La fonction JSON_EXTRACT est généralement utilisée pour obtenir une donnée spécifique en JSON ou pour l'utiliser comme condition de jugement

.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!