如何使用Oracle SQL从指定JSON字符串提取交易总笔数与交易总和值

Oracle 12c及以上版本提供了原生JSON解析函数，可以直接通过JSON路径表达式提取目标字段，不需要额外依赖。

单元素数组场景直接提取写法 #

当前示例中content数组仅包含1个对象，直接用JSON_VALUE即可快速取值，注意键名包含空格，在路径中需要用双引号包裹特殊键名：

SELECT
  JSON_VALUE(你的JSON字段名, '$.content[0]."Total No. of Transaction"' RETURNING NUMBER) AS 交易总笔数,
  JSON_VALUE(你的JSON字段名, '$.content[0]."Sum of Transaction"' RETURNING NUMBER) AS 交易总和
FROM 你的业务表名;

如果只是做语句测试，可以直接把JSON字符串作为入参，执行后会直接返回结果：

SELECT
  JSON_VALUE(
    '{
      "timestamp": 1654752742887,
      "status": "OK",
      "statusCode": 200,
      "message": "GET response successful.",
      "content": [
        {
          "Total No. of Transaction": 1,
          "Sum of Transaction": 473
        }
      ]
    }',
    '$.content[0]."Total No. of Transaction"' RETURNING NUMBER
  ) AS total_trans_cnt,
  JSON_VALUE(
    '{
      "timestamp": 1654752742887,
      "status": "OK",
      "statusCode": 200,
      "message": "GET response successful.",
      "content": [
        {
          "Total No. of Transaction": 1,
          "Sum of Transaction": 473
        }
      ]
    }',
    '$.content[0]."Sum of Transaction"' RETURNING NUMBER
  ) AS sum_trans_amt
FROM dual;

上述语句执行返回结果：

多元素数组场景写法 #

如果后续content数组可能包含多个对象，推荐使用JSON_TABLE做数组展开解析，兼容性更强：

SELECT jt.total_trans_cnt, jt.sum_trans_amt
FROM 你的业务表名 t,
JSON_TABLE(
  t.你的JSON字段名,
  '$.content[*]'
  COLUMNS (
    total_trans_cnt NUMBER PATH '$."Total No. of Transaction"',
    sum_trans_amt NUMBER PATH '$."Sum of Transaction"'
  )
) jt;

注意事项 #

• 上述原生JSON函数仅支持Oracle 12c R1及以上版本，11g及更早版本无原生JSON解析能力，需要借助自定义PL/SQL解析包实现，性能较差，不推荐在低版本中做JSON字段的在线解析查询
• JSON路径中如果键名包含空格、特殊字符，必须用双引号包裹键名，否则会报路径语法错误
• content为数组类型，[0]代表取数组第一个元素，[*]代表遍历数组所有元素

内容的提问来源于stack exchange，提问作者Md. Sajjad Hussain