Oracle JSON 与 XML 处理

适用版本：Oracle Database 12c / 18c / 19c / 21c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle 12c+ 原生支持 JSON[1]，XMLType 支持 XML[2]：

2. JSON 存储

2.1 12c（CLOB/VARCHAR2）

CREATE TABLE docs (
  id NUMBER PRIMARY KEY,
  data CLOB CHECK (data IS JSON)
);

INSERT INTO docs VALUES (
  1, 
  '{"name":"Alice","age":30,"city":"NYC"}'
);

2.2 21c+（原生 JSON 类型）

CREATE TABLE docs (
  id NUMBER PRIMARY KEY,
  data JSON
);

INSERT INTO docs VALUES (
  1, 
  '{"name":"Alice","age":30,"city":"NYC"}'
);

3. JSON 查询

3.1 简单点操作

-- 12c+
SELECT data.name FROM docs;
SELECT data.age FROM docs WHERE id = 1;

-- 路径
SELECT data.address.city FROM docs;

3.2 JSON_VALUE

SELECT JSON_VALUE(data, '$.name') AS name FROM docs;
SELECT JSON_VALUE(data, '$.age') AS age FROM docs;
SELECT JSON_VALUE(data, '$.address.city') AS city FROM docs;

3.3 JSON_QUERY

-- 返回 JSON 对象
SELECT JSON_QUERY(data, '$.address') FROM docs;

-- 数组
SELECT JSON_QUERY(data, '$.hobbies') FROM docs;
SELECT JSON_QUERY(data, '$.hobbies[0]') FROM docs;
SELECT JSON_QUERY(data, '$.hobbies[*]') FROM docs;

3.4 JSON_EXISTS

SELECT * FROM docs WHERE JSON_EXISTS(data, '$.name');
SELECT * FROM docs WHERE JSON_EXISTS(data, '$.age ? (@ > 25)');

4. JSON 生成

4.1 JSON_OBJECT

SELECT JSON_OBJECT(
  'id' VALUE employee_id,
  'name' VALUE last_name,
  'salary' VALUE salary
) AS emp_json
FROM employees;

4.2 JSON_ARRAY

SELECT JSON_ARRAY(
  'Alice', 'Bob', 'Charlie'
) FROM dual;

-- 表数据
SELECT JSON_ARRAYAGG(last_name) FROM employees WHERE dept_id = 10;
-- ["Alice","Bob","Charlie"]

4.3 JSON_OBJECTAGG

SELECT JSON_OBJECTAGG(key VALUE value) FROM ...

5. JSON 索引

5.1 内存索引

CREATE SEARCH INDEX idx_docs_json ON docs (data) FOR JSON;

5.2 函数索引

CREATE INDEX idx_docs_name ON docs (JSON_VALUE(data, '$.name'));

6. JSON 表

6.1 JSON_RELATIONAL（21c+）

-- JSON 数据当关系表查询
SELECT name, age FROM docs;

6.2 JSON_TABLE

SELECT jt.name, jt.age, jt.city
FROM docs, JSON_TABLE(data, '$' COLUMNS (
  name VARCHAR2(100) PATH '$.name',
  age NUMBER PATH '$.age',
  city VARCHAR2(50) PATH '$.city'
)) jt;

7. XML 处理

7.1 XMLType 存储

CREATE TABLE xml_docs (
  id NUMBER PRIMARY KEY,
  content XMLTYPE
);

INSERT INTO xml_docs VALUES (
  1, 
  XMLTYPE('<employee><name>Alice</name><age>30</age></employee>')
);

7.2 查询 XML

-- EXTRACT
SELECT EXTRACT(content, '/employee/name/text()').getStringVal()
FROM xml_docs;

-- XMLQuery
SELECT XMLQuery('/employee/name/text()' PASSING content RETURNING CONTENT)
FROM xml_docs;

-- XMLTable
SELECT xt.name, xt.age
FROM xml_docs, XMLTABLE('/employee' PASSING content COLUMNS (
  name VARCHAR2(100) PATH 'name',
  age NUMBER PATH 'age'
)) xt;

7.3 生成 XML

-- XMLELEMENT
SELECT XMLELEMENT("employee", 
  XMLFOREST(last_name AS name, salary AS sal)
) FROM employees;

-- XMLAGG
SELECT XMLAGG(XMLELEMENT("name", last_name) ORDER BY last_name)
FROM employees WHERE dept_id = 10;

8. 性能优化

8.1 JSON 索引

-- 搜索索引
CREATE SEARCH INDEX idx_json ON docs(data) FOR JSON;

-- 函数索引
CREATE INDEX idx_json_name ON docs(JSON_VALUE(data, '$.name'));

8.2 JSON_TABLE 替代多次查询

-- 一次解析
SELECT jt.name, jt.age, jt.city
FROM docs, JSON_TABLE(data, '$' COLUMNS (...)) jt;

9. 常见坑与排错

9.1 ORA-40441: JSON 语法错误

-- 检查 JSON 格式
-- 使用 JSON_VALID 验证（21c+）
SELECT JSON_VALID('{"name":"Alice"}') FROM dual;

9.2 性能差

-- 1. 加搜索索引
-- 2. 使用函数索引
-- 3. JSON_TABLE 批量解析

9.3 嵌套深

-- 路径表达式
$.address.city
$.hobbies[0]
$.friends[*].name

10. 最佳实践

21c+ 用 JSON 类型：原生
12c 用 CLOB + IS JSON：兼容
搜索索引提升：性能
JSON_TABLE 批量解析：效率
JSON_OBJECT 生成：标准
JSON_VALUE 简单值：明确
JSON_EXISTS 存在判断：高效
XML 用 XMLType：原生
XMLTable 关系化：查询
测试复杂 JSON：验证

11. 参考资料

[1] Oracle Database JSON Developer’s Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/

[2] Oracle Database XML DB Developer’s Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/