Oracle 12c解析JSON数组为关系表的正确SQL查询方法

错误原因 #

之前查询返回null均为JSON路径写法错误：

• 存储的JSON根节点是数组，不是单个对象，直接用JSON_VALUE(ORDER_DATA, '$.di' )会尝试从根层级找di字段，匹配不到返回null
• 原有JSON_TABLE语句路径逻辑错误：根节点才是数组，di字段是每个数组元素内的普通数值字段，写'$.di[*]'属于路径层级完全颠倒

正确查询语句 #

通过JSON_TABLE指定遍历根数组路径，逐个映射对象内字段即可，di字段直接定义精度保留两位小数：

SELECT 
  jt.od,
  jt.md,
  jt.di
FROM order_date j,
JSON_TABLE(
  j.json_order_data,
  '$[*]' COLUMNS (
    od VARCHAR2(10) PATH '$.od',
    md VARCHAR2(10) PATH '$.md',
    di NUMBER(10,2) PATH '$.di'
  )
) jt;

语句说明 #

• '$[*]'为JSON遍历根路径，表示遍历根数组下的每一个元素（即每条订单对应的JSON对象）
• COLUMNS块内的路径是相对于当前遍历到的单个JSON对象，直接用$.字段名即可取到对应值
• di字段定义为NUMBER(10,2)类型，查询时会自动保留两位小数，满足格式要求
• Oracle 12c原生支持上述JSON_TABLE语法，不需要额外安装扩展，也不需要使用不兼容的JSON_EXTRACT()函数

查询结果 #

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