MySQL 从 5.7.8 版本开始支持原生 JSON 数据类型; 这是一种非常实用的类型, 允许我们免于设计复杂的表关系结构, 从而更加专注于业务本身;

查询指定的 json 字段

json 内容举例:

1
2
3
4
{
    "type": "XXX",
    "content": "YYY"
}

查询条件为 json 对象中指定字段是否为指定的值:

1
SELECT * FROM `table_name` WHERE `field_name`->'$.type' = 'XXX'

查询 json 数组是否存在指定 value

json 内容举例:

1
[1, 2, 3, 4, 5]

查询条件为 json 数组中是否存在指定的值:

1
2
SELECT * FROM `table_name` WHERE json_contains(`field_name`, '4')
SELECT * FROM `table_name` WHERE json_contains(`field_name`, json_array(1, 4))

查询 json 对象是否存在指定 key

json 内容举例:

1
2
3
4
5
6
7
8
9
{
  "type" : "XXX",
  "content" : {
    "subField1" : 1,
    "subField2" : 2,
    "subField3" : 3,
    "subField4" : 4
  }
}

查询条件为 json 对象中是否存在指定的字段 (json_contains_path 函数第二个入参表示 anyMatch 或者 allMatch):

1
2
3
4
5
SELECT * FROM `table_name` WHERE json_contains_path(`field_name` -> '$.content', 'one', '$."subField2"')
// subField1 和 subField5 只要存在一个就匹配
SELECT * FROM `table_name` WHERE json_contains_path(`field_name` -> '$.content', 'one', '$."subField1"', '$."subField5"')
// subField1 和 subField5 必须都存在才匹配
SELECT * FROM `table_name` WHERE json_contains_path(`field_name` -> '$.content', 'all', '$."subField1"', '$."subField5"')

拼接 json

语法: JSON_INSERT(json, path1, value1[, path2, value2] …)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select version, gmtModified, recordStatus, spec, recordType from (
    select  revision as version, 
            gmt_modified as gmtModified,
            CASE WHEN record_status = 'LATEST' THEN 'LATEST' ELSE 'OUTDATED' END as recordStatus,
            JSON_INSERT('{}', '$.meta', api_meta, '$.traffic', traffic_distributions, '$.policies', policies) as spec,
            'draft' as type 
    from `ultramax_api_draft_history` where name = 'RPC^com.mtop.custom.type^1.0'
    union all
    select  version as version,
            gmt_modified as gmtModified,
            CASE WHEN record_status = 'VALID' THEN 'LATEST' ELSE 'OUTDATED' END as recordStatus,
            JSON_INSERT('{}', '$.meta', api_spec, '$.traffic', traffic_distributions, '$.policies', plan) as spec,
            'daily_snapshot' as type
    from `ultramax_api_snapshot_history_daily` WHERE `name` = 'RPC^com.mtop.custom.type^1.0'
  ) as merge
ORDER BY t desc, v desc
limit 10

参考资料