如何查询Oracle含JSON列的表，将JSON值转为表格格式并聚合统计cost值

假设你使用的是Oracle 12c及以上版本（支持原生JSON操作），你的业务表名为your_table，存储JSON blob的列名为json_col，可直接使用如下查询：

SELECT
  -- 统计line_item_a三个type下的cost总和
  NVL(JSON_VALUE(json_col, '$.type_a[0].line_item_a.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) +
  NVL(JSON_VALUE(json_col, '$.type_b[0].line_item_a.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) +
  NVL(JSON_VALUE(json_col, '$.type_c[0].line_item_a.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) AS line_item_a,
  -- 统计line_item_b三个type下的cost总和
  NVL(JSON_VALUE(json_col, '$.type_a[0].line_item_b.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) +
  NVL(JSON_VALUE(json_col, '$.type_b[0].line_item_b.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) +
  NVL(JSON_VALUE(json_col, '$.type_c[0].line_item_b.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) AS line_item_b,
  -- 统计line_item_c三个type下的cost总和
  NVL(JSON_VALUE(json_col, '$.type_a[0].line_item_c.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) +
  NVL(JSON_VALUE(json_col, '$.type_b[0].line_item_c.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) +
  NVL(JSON_VALUE(json_col, '$.type_c[0].line_item_c.cost' RETURNING NUMBER DEFAULT 0 ON ERROR), 0) AS line_item_c
FROM your_table;

说明 #

• 路径中的[0]适配你给出的JSON结构：每个type_a/type_b/type_c数组仅包含一个对象，所以取索引为0的元素
• DEFAULT 0 ON ERROR和NVL函数用于兼容字段缺失、值为null的场景，避免求和结果返回空值
• 若需要对整张表的所有行做全局聚合，可在外层对应列添加SUM()函数即可
• Oracle 12c以下版本无原生JSON支持，需升级数据库版本或安装第三方JSON解析包后使用

内容的提问来源于stack exchange，提问作者Vijay Shanker Dubey