在 Oracle 21c 中,對 JSON 的支持得到了進一步增強,提供了更加直接和高效的 JSON 操作功能。以下是一些常見的 JSON 操作範例,展示如何在 Oracle 21c 中使用 JSON 功能。
CREATE TABLE employees (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR2(100),
data CLOB CHECK (data IS JSON)
);
INSERT INTO employees (name, data)
VALUES ('Alice', '{"age": 30, "department": "HR", "skills": ["communication", "recruitment"]}');
SELECT name,
JSON_VALUE(data, '$.age') AS age,
JSON_VALUE(data, '$.department') AS department
FROM employees;
UPDATE employees
SET data = JSON_SET(data, '$.skills[1]', 'talent acquisition')
WHERE name = 'Alice';
SELECT *
FROM JSON_TABLE(
(SELECT data FROM employees WHERE name = 'Alice'),
'$'
COLUMNS (
age NUMBER PATH '$.age',
department VARCHAR2(50) PATH '$.department',
skills VARCHAR2(100) PATH '$.skills[*]'
)
);
CREATE INDEX idx_employee_skills ON employees (data)
INDEXTYPE IS JSON;
SELECT JSON_AGG(
JSON_OBJECT(
'id' VALUE id,
'name' VALUE name,
'age' VALUE JSON_VALUE(data, '$.age'),
'department' VALUE JSON_VALUE(data, '$.department')
)
) AS employees_json
FROM employees;