如何对Redshift SUPER类型字段执行文本/正则搜索及字符串操作

问题场景 #

• 暂存schema下的表中存在SUPER类型的JSON存储字段，承载JSON内容的字段名为elements
• 清洗后的表中曾尝试将该字段强转为VARCHAR类型，用于搜索和字符串函数操作
• 需求为在JSON内容中搜索字符串net，确定过滤用的键值对
• 初始执行的SQL如下：

select 
    elements
    , elements_raw
from clean.events
where 1=1
  and lower(elements) like '%net%'
  or strpos(elements,'net')

异常表现 #

• 上述查询始终返回空结果集
• 将查询字段替换为elements_raw执行时，收到报错：ERROR: function strpos(super, "unknown") does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.
• 查阅Redshift SUPER类型官方文档，未找到针对SUPER类型做内部字符串搜索的明确方案

预期目标 #

• 可在SUPER类型字段上正常执行字符串操作
• 掌握SUPER类型字段转字符串类型的正确方法

解决方案 #

核心问题原因 #

1. SUPER是Redshift专属的半结构化数据类型，不支持直接传入字符串类函数（like、strpos、lower等）做处理，直接调用就会触发函数不存在的报错
2. 直接用CAST(super_col AS VARCHAR)做类型转换存在兼容问题：当SUPER字段内存在嵌套对象、数组、数值、布尔类型值时，直接强转会出现序列化不完整、隐式转换失效的问题，导致模糊匹配不到内容，返回空结果
3. 初始SQL的条件逻辑存在优先级错误：SQL中AND运算优先级高于OR，原条件实际等价于(1=1 AND lower(elements) LIKE '%net%') OR strpos(elements,'net')，即使转换正确也可能返回不符合预期的结果
4. strpos函数返回匹配到的起始位置整数值，直接写在条件中虽然非0值会被判定为真，但显式标注> 0的判断条件兼容性更好

正确操作方法 #

SUPER类型转字符串的标准方案是使用json_serialize()函数，该函数会将任意SUPER类型值严格序列化为标准JSON格式的字符串，不存在内容截断或类型兼容问题。
修正后的可正常执行的SQL如下：

select 
    elements
    , elements_raw
from clean.events
where 
    lower(json_serialize(elements)) like '%net%'
    OR strpos(lower(json_serialize(elements_raw)), 'net') > 0

优化建议 #

如果搜索目标是JSON中固定key对应的value包含net，不建议直接对整个序列化后的JSON字符串做模糊匹配，性能较差，可以用SUPER原生的路径访问语法先提取对应字段，再做匹配，例如要匹配url字段值包含net的场景：

select 
    elements
    , elements_raw
from clean.events
where strpos(lower(json_serialize(elements_raw.url)), 'net') > 0

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