Presto中提取JSON任意顺序字段值的通用正则方案求助

问题分析 #

你当前的正则仅匹配字段值后带逗号的情况，无法处理字段位于对象末尾（值后为}）的场景，且字段顺序不固定，需要适配任意位置的字段提取。

通用正则方案 #

针对每个目标字段，修改正则表达式，让它同时匹配值后的逗号或右大括号，且精准捕获双引号包裹的字段值：

提取单个字段的示例

比如提取def字段：

REGEXP_EXTRACT(
    JSON_EXTRACT_SCALAR(Opfields, '$.value'),
    '.*"def":"([^"]*)"(?:,|})',
    1
) AS def

同理，提取ghi或qbc字段只需替换正则中的字段名：

-- 提取ghi字段
REGEXP_EXTRACT(
    JSON_EXTRACT_SCALAR(Opfields, '$.value'),
    '.*"ghi":"([^"]*)"(?:,|})',
    1
) AS ghi

-- 提取qbc字段
REGEXP_EXTRACT(
    JSON_EXTRACT_SCALAR(Opfields, '$.value'),
    '.*"qbc":"([^"]*)"(?:,|})',
    1
) AS qbc

正则逻辑说明

• .*"def":"：匹配字段名前的任意内容，精准定位目标字段的起始位置
• ([^"]*)：捕获字段值，匹配双引号内的所有内容（排除双引号本身）
• "(\?:,|})：匹配值的结束双引号，同时兼容后面的逗号（中间字段）或右大括号（末尾字段），(?:...)是不捕获的分组，避免干扰值的提取

更可靠的替代方案：用Presto原生JSON解析函数 #

正则处理JSON存在局限性（比如字段值包含转义双引号时会失效），推荐直接用Presto的JSON_PARSE解析嵌套的JSON字符串，再通过JSON路径提取字段，稳定性和可读性更强：

SELECT
    JSON_EXTRACT_SCALAR(
        JSON_PARSE(JSON_EXTRACT_SCALAR(Opfields, '$.value')),
        '$.123.payload[0].values.qbc'
    ) AS qbc,
    JSON_EXTRACT_SCALAR(
        JSON_PARSE(JSON_EXTRACT_SCALAR(Opfields, '$.value')),
        '$.123.payload[0].values.def'
    ) AS def,
    JSON_EXTRACT_SCALAR(
        JSON_PARSE(JSON_EXTRACT_SCALAR(Opfields, '$.value')),
        '$.123.payload[0].values.ghi'
    ) AS ghi
FROM your_table;

逻辑说明

1. JSON_EXTRACT_SCALAR(Opfields, '$.value')：提取外层JSON中value对应的转义JSON字符串
2. JSON_PARSE(...)：把转义的字符串解析为可操作的JSON对象
3. JSON_EXTRACT_SCALAR(..., '$.123.payload[0].values.xxx')：通过JSON路径直接提取目标字段值，不受字段顺序影响

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