如何过滤SQL中的JSON列 筛选指定字段值不为NULL的记录

不同数据库的JSON处理函数存在差异，核心逻辑是通过原生JSON函数提取目标字段值，再做非空判断，注意需要同时覆盖两种空场景：一是JSON结构内NAME字段存了null字面量，二是NAME字段根本不存在、JSON格式异常导致的SQL层面NULL。不要用字符串模糊匹配的方式过滤，JSON格式的空格、字段顺序变化都会导致匹配错误。

• MySQL 5.7/8.x 正确写法
  MySQL中JSON_EXTRACT()（即->运算符）返回的是JSON类型值，JSON结构内的null会被识别为合法JSON值，不会被判定为SQL NULL，需要额外判断或者转成标量值后过滤：

  -- 推荐写法：用->>（即JSON_UNQUOTE+JSON_EXTRACT）返回标量值，
  -- JSON null、字段不存在的场景都会返回SQL NULL，直接判断非空即可
  SELECT * FROM 你的表名
  WHERE JSON->>'$.NAME' IS NOT NULL;
  

  如果用->运算符，需要额外加条件排除JSON null值：

  SELECT * FROM 你的表名
  WHERE JSON->'$.NAME' IS NOT NULL
    AND JSON_TYPE(JSON->'$.NAME') != 'NULL';
  

• PostgreSQL 正确写法
  PostgreSQL中->>运算符直接将JSON字段值提取为text类型，JSON null、字段不存在都会返回SQL NULL，直接判断即可：

  SELECT * FROM 你的表名
  WHERE JSON->>'NAME' IS NOT NULL;
  

• SQL Server 正确写法
  SQL Server用JSON_VALUE提取标量值，空场景会直接返回SQL NULL：

  SELECT * FROM 你的表名
  WHERE JSON_VALUE(JSON, '$.NAME') IS NOT NULL;
  

• SQLite 3.38+ 正确写法
  SQLite的json_extract不会把JSON null转成SQL NULL，需要额外判断字段类型：

  SELECT * FROM 你的表名
  WHERE json_extract(JSON, '$.NAME') IS NOT NULL
    AND json_type(JSON, '$.NAME') != 'null';
  

踩坑提醒：不要写类似WHERE JSON NOT LIKE '%"NAME": null%'的语句，这种写法完全不可靠，JSON格式化换行、字段顺序调整、值内包含匹配字符串都会导致查询结果错误，必须使用数据库内置的JSON处理函数。

内容的提问来源于stack exchange，提问作者Yash