MySQL中查询json格式的字段实例详解

MySQL中查询json格式的字段需要使用JSON函数。此处介绍几个常用的MySQL JSON函数。

JSON_EXTRACT

JSON_EXTRACT可以用来提取json中的某个值，其语法如下：

JSON_EXTRACT(json_obj, path)

其中，json_obj表示json对象，path表示要提取的值的路径。路径可以是简单的key或者是嵌套的，例如：

SELECT JSON_EXTRACT('{"name": "John", "age": 30, "city": "New York", "pets": [{"name": "Puppy", "age": 3}, {"name": "Kitty", "age": 2}]}' , '$.name');

其中'$.name'表示提取name的值。输出结果为"John"。

JSON_KEYS

JSON_KEYS可以获取json中的所有key，其语法如下：

JSON_KEYS(json_obj)

例如：

SELECT JSON_KEYS('{"name": "John", "age": 30, "city": "New York", "pets": [{"name": "Puppy", "age": 3}, {"name": "Kitty", "age": 2}]}' );

输出结果为["name", "age", "city", "pets"]。

示例1

以下是一个示例，将json格式的数据插入数据库中：

CREATE TABLE `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  `info` JSON,
  PRIMARY KEY (`id`)
);

INSERT INTO `users` (`name`,`info`)
VALUES ('John Doe', '{"age": 30, "city": "New York"}'),
       ('Jane Wilson', '{"age": 25, "city": "Los Angeles", "pets": [{"name": "Puppy", "age": 3}, {"name": "Kitty", "age": 2}]}');

其中info字段为json格式。

下面就可以使用JSON_EXTRACT来查询json中的值：

SELECT name, JSON_EXTRACT(info, '$.age') as age, JSON_EXTRACT(info, '$.city') as city FROM users;

其中'$.age'表示提取age的值，'$.city'表示提取city的值。查询结果如下：

name	age	city
John Doe	30	New York
Jane Wilson	25	Los Angeles

示例2

以下是一个示例，使用JSON_KEYS获取json中的所有key：

SELECT name, JSON_KEYS(info) as info_keys FROM users;

查询结果如下：

name	info_keys
John Doe	["age", "city"]
Jane Wilson	["age", "city", "pets[0]", "pets[1]"]

其中每个key都可以使用JSON_EXTRACT来提取具体的值。例如：

SELECT name, JSON_EXTRACT(info, '$.pets[1].name') as pet1_name FROM users WHERE JSON_SEARCH(info, 'one', 'Kitty') IS NOT NULL;

其中JSON_SEARCH(info, 'one', 'Kitty')用于查找Kitty是否出现在info字段中，如果返回NULL则表示没有出现。查询结果如下：

name	pet1_name
Jane Wilson	Kitty

以上就是MySQL中查询json格式的字段的实例详解。