Redshift+DBT中扁平化JSON字段部分字段返回空值求助

针对你遇到的Campaign Name和Subject字段提取为空的情况，给你几个排查和解决的方向：

1. 改用Redshift原生JSON提取函数 #

Redshift直接用点符号访问嵌套JSON中带空格的键时，经常会出现解析异常。建议换成json_extract_path_text函数来提取字段，这个函数对特殊字符键的支持更稳定：

SELECT 
        flow_document.attributes.event_properties."$_cohort$message_send_cohort" as cohort,    
        flow_document.attributes.event_properties."$message" as message,
        flow_document.attributes.event_properties as event_properties,    
        json_extract_path_text(flow_document.attributes.event_properties, 'Campaign Name') as campaign_name,
        json_extract_path_text(flow_document.attributes.event_properties, 'Subject') as email_subject
FROM {{ ref('events') }}

2. 确认JSON字段的数据类型 #

如果flow_document.attributes.event_properties是VARCHAR类型（不是Redshift的JSON类型），直接用点符号访问会失效。此时可以先将字段转换为JSON类型再访问：

SELECT 
        (flow_document.attributes.event_properties::JSON)."$_cohort$message_send_cohort" as cohort,    
        (flow_document.attributes.event_properties::JSON)."$message" as message,
        flow_document.attributes.event_properties as event_properties,    
        (flow_document.attributes.event_properties::JSON)."Campaign Name" as campaign_name,
        (flow_document.attributes.event_properties::JSON)."Subject" as email_subject
FROM {{ ref('events') }}

3. 验证实际JSON中的键名 #

即使你认为键名匹配，也可以用json_keys函数列出当前JSON里的所有键，确认是否存在大小写、空格或者特殊字符差异：

SELECT 
        json_keys(flow_document.attributes.event_properties) as all_json_keys
FROM {{ ref('events') }}
LIMIT 10

查看返回的键列表，确认Campaign Name和Subject是否和你预期的完全一致（比如有没有全角空格、大小写错误）。

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