MySQL JSON列提取JSON对象的值为数组

在本文中，我们将介绍MySQL中如何提取JSON列中JSON对象的值为数组。JSON（JavaScript Object Notation）数据格式在最近几年中已经广泛地使用在互联网应用程序中。而MySQL 5.7和更新版本提供了内置的JSON数据类型，它们可以通过JSON函数从JSON格式中访问或提取数据。当我们需要从MySQL JSON列中获取JSON对象的值为一个数组时，可以使用JSON函数来实现。

阅读更多：MySQL 教程

1. 什么是MySQL JSON类型

MySQL 5.7.8与2013年发布后，开始支持JSON类型。MySQL内置的JSON数据类型可以存储和处理JSON数据。JSON类型是按照JavaScript对象表示法进行处理的，内部会使用引擎特定的存储格式来优化查询效率。可以使用如下SQL语句在MySQL中创建一个JSON类型的列：

CREATE TABLE `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `info` json DEFAULT NULL,
  PRIMARY KEY (`id`)
);

2. JSON_EXTRACT和JSON_ARRAY函数

在MySQL中，可以使用JSON_EXTRACT函数来提取JSON对象中指定的值。JSON_EXTRACT函数的语法如下：

JSON_EXTRACT(json_doc, path[, path] ...)

其中，json_doc是存储JSON数据的列或表达式，path参数是一个或多个JSON对象的键名，可以使用类似$.key的语法来提取嵌套JSON对象中的值。

JSON_ARRAY函数能够创建一个JSON数组，它的语法如下：

JSON_ARRAY([value[,value]...])

value参数可以使任何可被转换为JSON的MySQL数据类型。JSON_ARRAY函数返回一个JSON数组。

3. 将MySQL JSON对象值提取为数组

下面我们来看一下如何将MySQL JSON列中的JSON对象的值提取为数组。假设我们有如下的表people：

+----+----------+-------------------------------------+
| id | name     | info                                |
+----+----------+-------------------------------------+
| 1  | John Doe | {"age":30,"email":"johndoe@test.com"}|
| 2  | Jane Doe | {"age":25,"email":"janedoe@test.com"}|
+----+----------+-------------------------------------+

现在我们需要从people表中的info列中提取email的值为一个数组。可以使用以下SQL查询语句实现：

SELECT 
    JSON_EXTRACT(info, '$.email') as email
FROM 
    people;

输出的结果如下所示：

+----------------------+
| email                |
+----------------------+
| "johndoe@test.com"   |
| "janedoe@test.com"   |
+----------------------+

我们可以看到，上面的查询结果返回了人员的email地址。但是这些email值并不是一个数组。为了将email的值返回为一个数组，可以改为以下的SQL查询语句：

SELECT 
    JSON_ARRAY(
        JSON_EXTRACT(info, '$.email')
    ) as emails
FROM 
    people;

输出的结果如下所示：

+------------------------+
| emails                 |
+------------------------+
| ["johndoe@test.com"]   |
| ["janedoe@test.com"]   |
+------------------------+

现在我们可以看到，使用JSON_ARRAY函数，我们将从people表中的info列中的email提取为JSON数组。

如果我们需要从info列的键名中提取多个值为数组，则可以按如下方式编写SQL查询语句：

SELECT 
    JSON_ARRAY(
        JSON_EXTRACT(info, ' $.email'), JSON_EXTRACT(info, '$ .age')
    ) as personal_info
FROM 
    people;

输出的结果如下所示：

+------------------------+
| personal_info          |
+------------------------+
| ["johndoe@test.com",30]|
| ["janedoe@test.com",25]|
+------------------------+

现在，我们person_info这一列返回了包含email和age两个值的JSON数组。这就是如何将MySQL JSON列中的JSON对象的值提取为数组的方法。

4. 使用JSON_UNQUOTE函数

在上面的查询结果中，我们注意到返回的JSON数组值是带有引号的字符串。如果我们需要将字符串转换为实际的数组，可以使用JSON_UNQUOTE函数。JSON_UNQUOTE函数的语法如下：

JSON_UNQUOTE(json_val)

其中，json_val是代表JSON格式的MySQL表达式或当前会话中的字符串。

下面是将JSON数组字符串转换为实际数组的SQL查询语句：

SELECT 
    JSON_UNQUOTE(
        JSON_ARRAY(
            JSON_EXTRACT(info, ' $.email'), JSON_EXTRACT(info, '$ .age')
        )
    ) as personal_info
FROM 
    people;

输出的结果如下所示：

+------------------------+
| personal_info          |
+------------------------+
| ["johndoe@test.com",30]|
| ["janedoe@test.com",25]|
+------------------------+

现在返回的personal_info值是实际的JSON数组，没有引号。

总结

本文中，我们介绍了将MySQL JSON列中JSON对象的值提取为数组的方法。我们使用了MySQL内置的JSON_EXTRACT、JSON_ARRAY、JSON_UNQUOTE等函数来实现这一目标。在使用这些函数时，我们需要了解MySQL JSON数据类型的基本知识。这篇文章中提供的查询语句可以帮助我们处理MySQL JSON列中的复杂数据。