Oracle JSON 处理详解

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

1. 概述

Oracle 提供 JSON 处理能力[1]：

特性：

JSON 数据类型（21c+）
JSON 函数
JSON 索引
JSON Relational Duality（23ai）

2. 存储

2.1 CLOB / VARCHAR2

CREATE TABLE documents (
  id NUMBER PRIMARY KEY,
  doc VARCHAR2(4000) CHECK (doc IS JSON)
);

2.2 JSON 类型（21c+）

CREATE TABLE documents (
  id NUMBER PRIMARY KEY,
  doc JSON
);

2.3 LOB

CREATE TABLE documents (
  id NUMBER PRIMARY KEY,
  doc CLOB CHECK (doc IS JSON)
);

3. 插入

-- 字符串
INSERT INTO documents VALUES (1, '{"name": "Alice", "age": 30}');

-- JSON
INSERT INTO documents VALUES (2, JSON '{"name": "Bob", "age": 25, "skills": ["SQL", "PL/SQL"]}');

-- 多层
INSERT INTO documents VALUES (3, '{
  "name": "Charlie",
  "address": {
    "city": "Beijing",
    "zip": "100000"
  },
  "phones": [
    {"type": "home", "number": "010-12345678"},
    {"type": "mobile", "number": "13900000000"}
  ]
}');

4. 查询

4.1 简单点

SELECT d.doc.name FROM documents d;
SELECT d.doc.age FROM documents d;

4.2 JSON_VALUE

SELECT JSON_VALUE(doc, '$.name') AS name,
       JSON_VALUE(doc, '$.age') AS age
FROM documents;

4.3 JSON_QUERY

-- 提取对象
SELECT JSON_QUERY(doc, '$.address' WITH WRAPPER) AS address
FROM documents WHERE id = 3;

-- 数组
SELECT JSON_QUERY(doc, '$.phones' WITH WRAPPER) AS phones
FROM documents WHERE id = 3;

4.4 JSON_TABLE

SELECT d.id, t.name, t.age, t.city
FROM documents d,
  JSON_TABLE(doc, '$'
    COLUMNS (
      name VARCHAR2(50) PATH '$.name',
      age NUMBER PATH '$.age',
      city VARCHAR2(50) PATH '$.address.city'
    )
  ) t;

4.5 数组展开

SELECT d.id, t.type, t.number
FROM documents d,
  JSON_TABLE(doc, '$.phones[*]'
    COLUMNS (
      type VARCHAR2(20) PATH '$.type',
      number VARCHAR2(20) PATH '$.number'
    )
  ) t
WHERE d.id = 3;

5. 函数

5.1 JSON_OBJECT

SELECT JSON_OBJECT('name' VALUE name, 'age' VALUE age) AS json
FROM employees;

-- 23ai
SELECT JSON_OBJECT(name, age, salary) AS json FROM employees;

5.2 JSON_ARRAY

SELECT JSON_ARRAY(1, 2, 3, 4) FROM dual;
-- [1,2,3,4]

SELECT JSON_ARRAYAGG(name) FROM employees WHERE dept_id = 10;
-- ["Alice","Bob","Charlie"]

5.3 JSON_MERGEPATCH

UPDATE documents 
SET doc = JSON_MERGEPATCH(doc, '{"age": 31}')
WHERE id = 1;

5.4 JSON_EXISTS

SELECT * FROM documents 
WHERE JSON_EXISTS(doc, '$.address.city');

SELECT * FROM documents 
WHERE JSON_EXISTS(doc, '$.phones[*]?(@.type == "mobile")');

5.5 JSON_EQUAL

SELECT * FROM documents 
WHERE JSON_EQUAL(doc, '{"name":"Alice","age":30}');

6. 索引

6.1 B-Tree 函数

CREATE INDEX idx_doc_name ON documents (JSON_VALUE(doc, '$.name'));

6.2 JSON Search Index

CREATE SEARCH INDEX idx_doc_search ON documents (doc);

6.3 多值索引（21c+）

CREATE INDEX idx_doc_phones ON documents (JSON_VALUE(doc, '$.phones[*].number' MULTI));

7. JSON Path

7.1 语法

$      根
.name  属性
[0]    数组索引
[*]    所有
..     递归
@      当前

7.2 示例

-- 路径
$.name
$.address.city
$.phones[0].number
$.phones[*].type
$.skills[0 to 2]
$.phones[type="mobile"].number

8. 查询条件

-- JSON_EXISTS
SELECT * FROM documents WHERE JSON_EXISTS(doc, '$.age?(@ > 25)');

-- JSON_VALUE 比较
SELECT * FROM documents WHERE JSON_VALUE(doc, '$.age') > 25;

-- JSON_TEXTCONTAINS（需要 Search Index）
SELECT * FROM documents WHERE JSON_TEXTCONTAINS(doc, '$.name', 'Alice');

9. 修改

9.1 JSON_MERGEPATCH

-- 添加
UPDATE documents SET doc = JSON_MERGEPATCH(doc, '{"email":"[email protected]"}') WHERE id = 1;

-- 修改
UPDATE documents SET doc = JSON_MERGEPATCH(doc, '{"age":31}') WHERE id = 1;

-- 删除（置 null）
UPDATE documents SET doc = JSON_MERGEPATCH(doc, '{"age":null}') WHERE id = 1;

9.2 JSON_TRANSFORM（21c+）

UPDATE documents SET doc = JSON_TRANSFORM(
  doc,
  SET '$.age' = 32,
  SET '$.email' = '[email protected]',
  REMOVE '$.phones'
) WHERE id = 1;

10. JSON Relational Duality（23ai）

10.1 创建视图

CREATE TABLE employees (id NUMBER PRIMARY KEY, name VARCHAR2(100), salary NUMBER);
CREATE TABLE departments (id NUMBER PRIMARY KEY, name VARCHAR2(100), emp_id NUMBER REFERENCES employees(id));

CREATE JSON DUALITY VIEW employees_dv AS
  SELECT e.id, e.name, e.salary,
    JSON_ARRAYAGG(
      JSON_OBJECT(d.id, d.name)
    ) AS departments
  FROM employees e
  LEFT JOIN departments d ON e.id = d.emp_id
  GROUP BY e.id, e.name, e.salary;

10.2 操作

-- 查询（JSON）
SELECT * FROM employees_dv;

-- 插入（JSON）
INSERT INTO employees_dv VALUES (JSON '{"id":1,"name":"Alice","salary":5000}');

-- 更新
UPDATE employees_dv SET salary = 6000 WHERE id = 1;

详细见：Oracle 23c 新特性 SQL。

11. 性能

11.1 存储

- JSON 类型（21c+）：二进制存储
- VARCHAR2：字符串
- LOB：大文档

11.2 索引

- B-Tree：精确查询
- Search Index：全文
- 多值索引：数组

11.3 查询

- JSON_VALUE：单值
- JSON_TABLE：多值
- JSON_QUERY：对象

12. 应用场景

12.1 API

- RESTful
- 半结构化
- 文档

12.2 配置

- 灵活配置
- 多语言

12.3 日志

- 结构化日志
- 嵌套

13. 常见坑与排错

13.1 ORA-40441

- JSON 语法错误
- 验证

13.2 ORA-40462

- JSON 路径错误
- 检查

13.3 性能

- 索引
- JSON 类型
- 减少全表

14. 最佳实践

JSON 类型（21c+）：性能
约束 IS JSON：质量
JSON_VALUE：单值
JSON_TABLE：多值
索引：性能
Search Index：全文
JSON_TRANSFORM：21c+
Duality：23ai
应用层：API
测试：验证

15. 参考资料

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