RedShift使用regexp_substr提取JSON键无返回结果问题咨询

问题原因 #

1. 正则表达式转义错误：RedShift 中字符串内的反斜杠本身是转义字符，你写的\S需要改为\\S才能被识别为「匹配非空白字符」的正则元字符，原语句中的正则表达式无法命中任何内容，所以返回空。
2. 正则提取逻辑缺陷：就算修正转义问题，regexp_substr 默认仅返回第一个匹配结果，无法一次性提取所有JSON键；另外如果JSON键内部包含空白字符、转义双引号等特殊内容，正则匹配会出现误判，可靠性极低。

解决方法 #

方法1：修正正则写法（仅适合简单场景，不推荐） #

如果一定要用正则实现，调整正则语法，单键提取的修正写法如下：

SELECT 
  REGEXP_SUBSTR(REPLACE(REPLACE(attributes, '{', ''), '}', '')::VARCHAR, '\\"[^\\"]+\\":') AS regexp,
  JSON_PARSE(attributes) AS attributes_super
FROM source.table
WHERE prompttype != 'input'

这里的正则\\"[^\\"]+\\":会严格匹配双引号包裹、后面跟冒号的键内容，比\S+:的匹配准确性更高。如果需要返回所有匹配的键，可以配合regexp_count和递归CTE实现多值返回。

方法2：使用Super类型内置方法提取（推荐） #

你已经将JSON字段转为Super类型，直接用RedShift原生的JSON键提取逻辑，性能和准确性都远高于正则：
如果要提取所有键拼成数组，写法如下：

SELECT 
  JSON_KEYS(JSON_PARSE(attributes)) AS all_keys,
  JSON_PARSE(attributes) AS attributes_super
FROM source.table
WHERE prompttype != 'input'

如果要把数组中的每个键拆分为单独行，配合UNNEST函数实现：

SELECT 
  key,
  attributes_super
FROM source.table,
UNNEST(JSON_KEYS(JSON_PARSE(attributes))) AS t(key)
WHERE prompttype != 'input'

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