如何在Oracle中解析JSON数据

我有一张表，其中的HUGECLOB列存储了JSON数据，想要解析这些数据应该怎么操作？示例JSON内容如下：

{"errors":{"destination_country_id":["can not be blank"],"dispatch_country_id":["can not be blank"],"vehicle_id":["can not be blank"],"trailer_id":["can not be blank"]}}

我尝试了以下SQL语句：

SELECT t.*
FROM table,
     JSON_TABLE(_hugeclob_data, '$'
          COLUMNS (destination_country_id  VARCHAR2(50 CHAR) PATH '$.destination_country_id',
                   dispatch_country_id     VARCHAR2(50 CHAR) PATH '$.dispatch_country_id',
                   vehicle_id              VARCHAR2(50 CHAR) PATH '$.vehicle_id',
                   trailer_id              VARCHAR2(50 CHAR) PATH '$.trailer_id'                  
                  )
              ) t;

问题分析与修正 #

你的SQL存在两个核心问题：

1. 路径指向错误：目标字段都在JSON根节点的errors对象下，当前用$指向根节点，无法直接取到errors内的字段；
2. 数据类型不匹配：每个字段的值是数组类型（比如["can not be blank"]），直接取路径会返回数组，无法匹配VARCHAR2类型的列定义。

另外，table是Oracle的关键字，作为表名时需要用双引号包裹，或给表起别名避免语法冲突。

修正后的SQL（提取单条错误信息） #

如果只需要每个字段的第一条错误信息，调整路径指向$.errors并指定数组下标[0]：

SELECT t.*
FROM "table" t_main,
     JSON_TABLE(t_main._hugeclob_data, '$.errors'
          COLUMNS (
              destination_country_id  VARCHAR2(50 CHAR) PATH '$.destination_country_id[0]',
              dispatch_country_id     VARCHAR2(50 CHAR) PATH '$.dispatch_country_id[0]',
              vehicle_id              VARCHAR2(50 CHAR) PATH '$.vehicle_id[0]',
              trailer_id              VARCHAR2(50 CHAR) PATH '$.trailer_id[0]'                  
          )
     ) t;

扩展：展开所有错误信息 #

如果某个字段可能有多个错误（数组含多个元素），可以用NESTED PATH展开数组，将每个错误拆成单独行：

SELECT t.error_field, t.error_message
FROM "table" t_main,
     JSON_TABLE(t_main._hugeclob_data, '$.errors.*'
          COLUMNS (
              error_field VARCHAR2(100 CHAR) PATH '$',
              NESTED PATH '[*]' COLUMNS (
                  error_message VARCHAR2(50 CHAR) PATH '$'
              )
          )
     ) t;

执行后结果示例：

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