MySQL 判断 JSON 中的值为空

[ChuChencheng]

背景

假设表中有一列数据是 json 类型，但可以为 null ，要判断 json 中的某个字段是否为空

方案

以 { "a": null } 这个 json 结构为例

首先列出为空的定义：

1. 字段值为 NULL
2. $.a === null
3. $.a 路径不存在
4. 其他情况，以 a 的类型决定，例如 $.a === [] 或者 $.a === {} 或者 $.a === ""

SQL 语句：

# 字段值为 NULL 或者 $.a 不存在
# 注意，这里的 null 是 MySQL NULL ，而不是 json 类型中的 null ，因此用 is null 判断
select json_type(json_extract(NULL, '$.a')) is null;
select json_type(json_extract('{}', '$.a')) is null;

# $.a === null
select json_type(json_extract('{"a": null}', '$.a')) = 'NULL';

# 剩下的就是其他情况了
# `$.a === []` 或者 `$.a === {}`
select json_length(json_extract('{"a": []}', '$.a')) = 0;

# `$.a === ""`
select json_contains('{"a": ""}', '""', '$.a') = 1;

在查询时，以上 select 后的语句视情况选取合适的几条，直接加入到 where 条件中，用 and 连接。