Athena如何基于嵌套JSON的布尔字段值执行查询过滤？

问题根因 #

之前的写法不生效，核心有两个原因：

• json_extract 函数返回值为JSON类型，无法直接和原生布尔值true做等值判断
• SQL执行顺序中WHERE子句优先级高于SELECT，同层级查询里不能直接使用SELECT中定义的字段别名

正确查询写法 #

写法1：直接在WHERE子句中处理（单场景使用推荐） #

直接对原表payload字段做标量提取+类型转换后过滤，性能最优：

SELECT 
  foodId, 
  foodType, 
  json_extract(payload,'$.food.info.seeds') AS hasSeeds
FROM "food_table"
WHERE 
  foodType = 'fruit' 
  -- 先提取标量值再转为布尔类型做判断
  AND CAST(json_extract_scalar(payload, '$.food.info.seeds') AS BOOLEAN) = true

写法2：CTE封装后过滤（多逻辑复用推荐） #

如果后续还有多个逻辑要用到hasSeeds字段，可以用CTE提前封装处理逻辑，代码可读性更高：

WITH fruit_base AS (
  SELECT 
    foodId, 
    foodType, 
    CAST(json_extract_scalar(payload, '$.food.info.seeds') AS BOOLEAN) AS hasSeeds
  FROM "food_table"
  WHERE foodType = 'fruit'
)
SELECT * FROM fruit_base WHERE hasSeeds = true

之前错误写法说明 #

之前的CTE版本问题在于，额外定义了固定值的dataset表，和原food_table做了笛卡尔积关联，只要固定JSON的seeds值为true，所有水果条目都会被返回，没有对原表的payload字段做判断，自然无法实现过滤效果。

以上两种写法对示例数据执行后，只会返回foodId=1的苹果条目，完全符合筛选有种子水果的需求。

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