PostgreSQL环境下嵌套JSON日期值提取异常求助

假设你的JSON嵌套结构类似（对应你提供的截图内容）：

{
  "上层字段": {
    "Value": "202X-XX-XX"
  }
}

以下是具体提取和转换方法：

1. 提取嵌套的日期字符串 #

如果你的字段是JSON或JSONB类型，使用->>运算符按层级提取Value对应的字符串值：

-- 假设表名为your_table，JSON字段名为json_column，上层字段名为upper_field
SELECT json_column->'upper_field'->>'Value' AS date_str
FROM your_table;

如果是更深层级的嵌套，或者想直接用路径提取，可使用#>>运算符：

SELECT json_column#>>'{upper_field, Value}' AS date_str
FROM your_table;

2. 转换为PostgreSQL日期类型 #

提取出的字符串需转换为date类型才能进行日期相关操作：

SELECT (json_column#>>'{upper_field, Value}')::date AS extracted_date
FROM your_table;

特殊场景处理 #

• 如果日期格式不是PostgreSQL默认的YYYY-MM-DD，用TO_DATE函数指定格式：

  SELECT TO_DATE(json_column#>>'{upper_field, Value}', 'MM/DD/YYYY') AS extracted_date
  FROM your_table;
  

• 若JSON结构存在缺失值，用COALESCE处理空值避免报错：

  SELECT COALESCE((json_column#>>'{upper_field, Value}')::date, '1900-01-01'::date) AS extracted_date
  FROM your_table;
  

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