MySQL查询JSON¶

备注

本文是一个实践案例，更为详细的MySQL JSON数据处理，请参考在MariaDB中使用JSON

JSON数据¶

在生产环境中，应用数据交换往往会采用 JSON 进行。这些交换数据可能会存放在数据库中，形成一条JSON(字段)记录，例如:

MySQL记录中的JSON数据记录举例¶

[
  {
    "alert_name": "alert:key_component_wrong_leader_number",
    "cluster": "cluster-1"
  },
  {
    "alert_name": "alert:key_component_wrong_leader_number",
    "cluster": "cluster-1"
  }
]

这里有一个问题，我们的告警记录会合并多条相同告警到一个JSON记录中，对于后期统计报表(从mysql查询)，我们实际上只关注这个JSON数组中第一个值(后面都是重复的)

备注

如果使用阿里云的 MaxCompute ODPS SQL 支持 ODPS SQL处理JSON数据，也有类似函数 json_extract

测试数据¶

我采用 How to Query JSON column in MySQL 案例来学习实践:

先快速安装MariaDB ，初始化一个简单的测试数据库
创建一个 json 字段的:

创建包含 json 数据的简单表¶

create table users(
    id int auto_increment primary key,
    details json
);

插入数据库测试数据:

插入测试数据¶

insert into users(details)
    values(
      '{ "page": "/" , 
         "name": "Safari", 
         "os": "Mac", 
         "spend": [100, 50],
         "resolution": { "x": 1920, "y": 1080 } }'
),
(
      '{ "page": "/products", 
         "name": "Chrome", 
         "os": "Windows", 
         "spend": [150, 250],
         "resolution": { "x": 1680, "y": 1050 } }'
),
(
     '{ "page": "/shoes", 
        "name": "Firefox", 
        "os": "Windows", 
        "spend": [200,300],
        "resolution": { "x": 1280, "y": 800 } }'
);

备注

这里我遇到一个在MariaDB中使用JSON 相关的报错 MariaDB ERROR 4025 (23000): CONSTRAINT ... ，原因是 How to Query JSON column in MySQL 提供的SQL源代码有一些格式错误( JSON 的object形式需要通过 , 分隔每个键值 )，我通过 jq 工具检查和校对(上文代码已经修正)

查询插入的json数据:

查询json测试数据¶

select * from users;

输出如下:

查询json测试数据输出结果¶

+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | details                                                                                                                                                     |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | { "page": "/" ,
         "name": "Safari",
         "os": "Mac",
         "spend": [100, 50],
         "resolution": { "x": 1920, "y": 1080 } }             |
|  2 | { "page": "/products",
         "name": "Chrome",
         "os": "Windows",
         "spend": [150, 250],
         "resolution": { "x": 1680, "y": 1050 } } |
|  3 | { "page": "/shoes",
        "name": "Firefox",
        "os": "Windows",
        "spend": [200,300],
        "resolution": { "x": 1280, "y": 800 } }         |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

一切就绪，我们开始尝试检索(retrieve) JSON字段数据

`JSON_EXTRACT` 函数¶

从MySQL version >= 5.7 开始，提供了 JSON_EXTRACT 函数可以用来检索JSON数据:

使用 JSON_EXTRACT 函数检索JSON¶

select id, 
       json_extract(details,"$.name") as browser_name,
       json_extract(details,"$.os") as browser_os
       from users;

此时就会看到提取出对应 key 的 value :

使用 JSON_EXTRACT 函数检索JSON输出案例¶

+----+--------------+------------+
| id | browser_name | browser_os |
+----+--------------+------------+
|  1 | "Safari"     | "Mac"      |
|  2 | "Chrome"     | "Windows"  |
|  3 | "Firefox"    | "Windows"  |
+----+--------------+------------+

处理array¶

回到本文开头提到的告警数据，是一个 JSON 的array结构:

MySQL记录中的JSON数据记录举例¶

[
  {
    "alert_name": "alert:key_component_wrong_leader_number",
    "cluster": "cluster-1"
  },
  {
    "alert_name": "alert:key_component_wrong_leader_number",
    "cluster": "cluster-1"
  }
]

对于这个案例，我需要提取出 array[0] 中的object的键值，则在 JSON_EXTRACT 函数中使用 $[index].key 方式，也就是先指定数组下标(array index)，然后再取出key对应的value。这样语句就是:

使用 JSON_EXTRACT 函数检索JSON的array¶

select id, 
       json_extract(alert_json,"$[0].alert_name") as alert_name
       from alert_notifier;