如何在Redshift SQL中提取动态长度字符串里的val2值？

方案1：使用正则表达式（适用于非标准JSON格式的字段） #

如果column1存储的是类JSON字符串但不符合标准JSON规范，可以用正则匹配提取val2的值。

Oracle 实现

SELECT 
    column1,
    REGEXP_SUBSTR(column1, '''val2'': ''([^'']+)''', 1, 1, 'i', 1) AS column2
FROM your_table;

• 正则逻辑：'''val2'': ''' 匹配字符串中的'val2': '片段，([^'']+)捕获单引号之间的所有内容（即val2对应的值），最后一个参数1指定提取第一个捕获组的内容。

MySQL 8.0+ 实现

SELECT 
    column1,
    REGEXP_REPLACE(column1, '.*''val2'': ''([^'']+)''.*', '$1') AS column2
FROM your_table;

• 逻辑说明：用.*匹配val2前后的所有内容，将整个字符串替换为捕获到的val2值。

方案2：使用JSON函数（适用于标准JSON格式的字段） #

如果column1是标准JSON类型（或可解析为合法JSON），用JSON函数会更可靠，避免正则的局限性：

Oracle 实现

SELECT 
    column1,
    JSON_VALUE(column1, '$[0].val2') AS column2
FROM your_table;

• 说明：$[0]定位JSON数组中的第一个对象，.val2提取该对象的val2属性值。

MySQL 实现

SELECT 
    column1,
    column1->>'$[0].val2' AS column2
FROM your_table;

• 简化写法：->>等价于JSON_UNQUOTE(JSON_EXTRACT(...))，直接返回无引号的val2值。

测试验证 #

针对你提供的输入：

输入column1值：[{'val1': '54', 'val2': 'luis long, F (Peter)', 'val3': 'xxx@email.com', 'val4': 'xxxxyyy://somevalue', 'val5': 'Category', 'val6': 'some other value'}]

上述方案都会输出：

• column1：保留原内容
• column2：luis long, F (Peter)

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