AWS Redshift中json_extract_path_text查表返回空串而非NULL的解决方法

你遇到的差异是Redshift在处理JSON字符串常量和表中VARCHAR类型JSON字段时，对不存在路径的返回逻辑不同：

• 直接传入JSON字符串常量时，函数返回NULL
• 解析表内VARCHAR类型的JSON字段时，未找到目标路径会返回空字符串

要让表查询场景下返回NULL，可采用以下两种方案：

方案1：用NULLIF将空字符串转换为NULL #

通过NULLIF函数把函数返回的空字符串替换成NULL：

SELECT NULLIF(json_extract_path_text(payload, 'AA'), '') FROM #test;

方案2：先将字段转为JSON类型再解析 #

Redshift原生支持JSON数据类型，先把VARCHAR类型的payload转换为JSON类型后再解析，此时未找到路径会直接返回NULL：

-- 使用json_extract_path_text
SELECT json_extract_path_text(CAST(payload AS JSON), 'AA') FROM #test;
-- 或使用更简洁的->>操作符（效果一致）
SELECT CAST(payload AS JSON)->>'AA' FROM #test;

补充说明 #

如果你的payload字段本身已经是JSON类型，直接使用json_extract_path_text或->>操作符查询不存在的路径，会默认返回NULL，无需额外处理。

内容的提问来源于stack exchange，提问作者Idan b.y