Athena SQL：从字符串提取JSON字段，不存在时返回NULL

我有一张表json_licensedata，其中Validity列存储的是类JSON格式的字符串（注意键值对用=而非标准JSON的:，键名也没有引号），示例数据如下：

{begin=2023-10-27T16:13:36.756Z, end=2026-10-27T16:13:36.756Z} 
{begin=2023-10-28T16:13:36.756Z}

执行查询SELECT validity.begin, validity.end FROM json_licensedata时，无法正确提取字段——当end不存在时，用CASE WHEN处理也失败，期望得到如下结果：

问题根源是Validity存储的不是标准JSON，直接按JSON解析会报错或无法识别字段。可以通过两种方式处理：

方式1：转换为标准JSON后提取 #

先把类JSON字符串转换成标准JSON格式，再用JSON函数提取字段：

MySQL 示例 #

SELECT
    JSON_UNQUOTE(JSON_EXTRACT(REPLACE(REPLACE(Validity, '{', '{"'), '=', '":'), '$.begin')) AS `Begin`,
    JSON_UNQUOTE(JSON_EXTRACT(REPLACE(REPLACE(Validity, '{', '{"'), '=', '":'), '$.end')) AS `End`
FROM json_licensedata;

通过两次REPLACE将类JSON转换为标准格式：把开头的{替换成{"，把所有=替换成":，再用JSON_EXTRACT提取字段，JSON_UNQUOTE去除结果的引号。

PostgreSQL 示例 #

SELECT
    jsonb_extract_path_text(
        regexp_replace(Validity, '(\w+)=', '"\1":', 'g')::jsonb,
        'begin'
    ) AS "Begin",
    jsonb_extract_path_text(
        regexp_replace(Validity, '(\w+)=', '"\1":', 'g')::jsonb,
        'end'
    ) AS "End"
FROM json_licensedata;

用regexp_replace给所有键名加上引号、把=替换为:，转成jsonb类型后提取字段，不存在的字段会自动返回NULL。

方式2：直接用字符串正则提取 #

如果不想转换JSON，直接用正则匹配提取对应值，这种方法更通用，不依赖JSON函数：

SELECT
    TRIM(SUBSTRING(Validity FROM 'begin=([^,}]+)')) AS `Begin`,
    CASE
        WHEN Validity ~ 'end=' THEN TRIM(SUBSTRING(Validity FROM 'end=([^}]+)'))
        ELSE NULL
    END AS `End`
FROM json_licensedata;

• 用正则begin=([^,}]+)匹配begin=后面到逗号或大括号的内容，提取后去除空格；
• 先判断字符串是否包含end=，存在则提取对应值，否则返回NULL，避免字段不存在导致的错误。

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