MySQL中查询JSON字段pushedToSnowflakePipeline='no'无结果的问题

我有一张名为t_records的表，其中包含JSON类型的列col_json，该列的示例值如下：

{
  "1": {
    "value": null,
    "comment": null,
    "timestamp": null,
    "guidelineId": null,
    "pushedToSnowflakePipeline": "yes"
  },
  "2": {
    "value": null,
    "comment": null,
    "timestamp": null,
    "guidelineId": null,
    "pushedToSnowflakePipeline": "yes"
  },
  "3": {
    "value": null,
    "comment": null,
    "timestamp": null,
    "guidelineId": null,
    "pushedToSnowflakePipeline": "no"
  }
}

我想要查询其中存在pushedToSnowflakePipeline='no'的行，但使用以下查询语句未返回任何记录：

SELECT * 
FROM t_records 
WHERE JSON_EXTRACT(col_json, '$.*.pushedToSnowflakePipeline')='no';

请问我的查询语句哪里出错了？

JSON_EXTRACT(col_json, '$.*.pushedToSnowflakePipeline')返回的是一个数组，示例数据会得到["yes", "yes", "no"]。直接将这个数组和字符串'no'做相等判断，逻辑上不成立，所以查不到结果。

根据不同数据库，提供几种可行的写法：

1. MySQL环境 #

用JSON包含判断 #

SELECT * 
FROM t_records 
WHERE JSON_CONTAINS(JSON_EXTRACT(col_json, '$.*.pushedToSnowflakePipeline'), '"no"');

用JSON路径存在判断（MySQL 8.0+支持） #

SELECT * 
FROM t_records 
WHERE JSON_PATH_EXISTS(col_json, '$.*?(@.pushedToSnowflakePipeline == "no")');

2. Snowflake环境 #

推荐用FLATTEN展开JSON对象后过滤，同时用DISTINCT避免重复返回原行：

SELECT DISTINCT t.*
FROM t_records t,
     LATERAL FLATTEN(input => t.col_json) f
WHERE f.value:pushedToSnowflakePipeline::STRING = 'no';

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