Oracle 21c JSON 範例

發表於 2024-08-26 分類於 Oracle 閱讀次數：

在 Oracle 21c 中，對 JSON 的支持得到了進一步增強，提供了更加直接和高效的 JSON 操作功能。以下是一些常見的 JSON 操作範例，展示如何在 Oracle 21c 中使用 JSON 功能。

創建 JSON 表
首先，你可以創建一個包含 JSON 列的表。這樣的表可以存儲 JSON 格式的數據。

CREATE TABLE employees (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100),
    data CLOB CHECK (data IS JSON)
);

插入 JSON 數據
可以將 JSON 格式的數據插入到表中。Oracle 支持使用 JSON 格式的字串直接插入到 CLOB 列。

1 2	INSERT INTO employees (name, data) VALUES ('Alice', '{"age": 30, "department": "HR", "skills": ["communication", "recruitment"]}');

查詢 JSON 數據
Oracle 21c 提供了多種 JSON 函數來查詢和操作 JSON 數據。例如，使用 JSON_VALUE 函數提取 JSON 列中的單個值。

SELECT name,
       JSON_VALUE(data, '$.age') AS age,
       JSON_VALUE(data, '$.department') AS department
FROM employees;

更新 JSON 數據
可以使用 JSON_SET 函數更新 JSON 列中的數據。例如，修改某個員工的技能列表。

1
2
3

UPDATE employees
SET data = JSON_SET(data, '$.skills[1]', 'talent acquisition')
WHERE name = 'Alice';

使用 JSON Table 函數
Oracle 21c 引入了 JSON_TABLE 函數，這是一種強大的方法，用於將 JSON 數據轉換為關聯數據表格式，從而更容易進行查詢和分析。

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[*]'
    )
);

範例：JSON 索引
Oracle 21c 支持對 JSON 列創建索引，提升查詢性能。例如，創建一個基於 JSON 列的索引。

1 2	CREATE INDEX idx_employee_skills ON employees (data) INDEXTYPE IS JSON;

範例：JSON 聚合
可以使用 JSON_AGG 函數來聚合查詢結果為 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;

總結

Oracle 21c 在 JSON 處理上提供了強大的功能，從創建 JSON 表、插入和更新 JSON 數據，到使用 JSON 表函數和索引，都能有效地處理 JSON 數據。這些功能使得在 Oracle 資料庫中操作和分析 JSON 數據變得更加方便和高效。

創建 JSON 表

插入 JSON 數據

查詢 JSON 數據

更新 JSON 數據

使用 JSON Table 函數

範例：JSON 索引

範例：JSON 聚合

總結