Redshift DBT中从类JSON字符串提取并修剪值的方法

针对你遇到的问题，这里提供一个可行的解决思路，先分析下你之前方法无效的原因，再给出完整方案：

之前方法无效的原因 #

• 第一种替换法只适配单个{'Title': 'xxx'}的情况，遇到多个元素的数组时，替换后会残留, {'Title': 'xxx'}这类内容，没法完整提取所有Title值。
• 第二种方法写错了列名（用了Id而非mycol），而且JSON_EXTRACT_PATH_TEXT只能处理单个JSON对象，没法解析数组结构，多元素场景下要么只取到第一个值，要么直接报错。

有效解决方案 #

核心思路是先把字符串转成合法JSON格式，再解析数组、展开元素，最后拼接所有Title值：

完整SQL示例（DBT模型中直接使用） #

WITH parsed_json AS (
    -- 将单引号替换成双引号，转成合法JSON数组
    SELECT
        mycol,
        JSON_PARSE(REPLACE(mycol, '''', '"')) AS json_array
    FROM your_table_name  -- 替换成你的实际表名
),
unnested_elements AS (
    -- 展开JSON数组中的每个元素，提取单个Title
    SELECT
        mycol,
        JSON_EXTRACT_PATH_TEXT(json_item, 'Title') AS single_title
    FROM parsed_json,
         UNNEST(json_array) AS t(json_item)
)
-- 拼接所有Title，单个元素时直接返回，多个则用逗号分隔
SELECT
    mycol,
    LIST_AGG(single_title, ', ') AS extracted_mycol
FROM unnested_elements
GROUP BY mycol;

特殊情况处理 #

如果你的Title值里存在转义的单引号（比如{'Title': 'O\'Neil'}），需要先处理转义再替换，把转义的单引号换成JSON标准的双引号转义：

REPLACE(REPLACE(mycol, '\'', '\\\''), '''', '"')

把上面这个替换逻辑替换掉示例中REPLACE(mycol, '''', '"')即可。

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