【Oracle】JSON完全ガイド｜JSON_VALUE・JSON_TABLE・JSON_OBJECT・IS JSON制約・23aiのネイティブJSON型まで実例で解説

【Oracle】JSON完全ガイド｜JSON_VALUE・JSON_TABLE・JSON_OBJECT・IS JSON制約・23aiのネイティブJSON型まで実例で解説 Oracle

Oracle は 12c からネイティブで JSON をサポートしており、VARCHAR2 や CLOB 列に JSON を格納し、SQL 関数でパース・検索・生成ができます。21c では専用の JSON データ型が追加され、23ai では JSON Relational Duality Views によりリレーショナルデータを JSON API として直接公開できるようになりました。

この記事でわかること

  • IS JSON 制約で JSON カラムを定義する方法
  • JSON_VALUE・JSON_QUERY で値とオブジェクトを取り出す
  • JSON_EXISTS で条件絞り込みをする
  • JSON_TABLE で JSON 配列を表形式に展開する
  • JSON_OBJECT・JSON_ARRAY・JSON_ARRAYAGG でクエリ結果を JSON に変換
  • Oracle 21c の JSON データ型（ネイティブバイナリ格納）
  • Oracle 23ai の JSON Relational Duality Views
スポンサーリンク

Oracle バージョン別 JSON 対応状況

バージョン 追加された主な機能
12c R1（2013） IS JSON 制約・JSON_VALUE・JSON_QUERY・JSON_TABLE・JSON_EXISTS の基本実装
12c R2（2016） JSON インデックス（JSON_VALUE ベース）の性能改善
18c / 19c JSON_OBJECT・JSON_ARRAY・JSON_ARRAYAGG・JSON_OBJECTAGG 関数の追加
21c（2021） ネイティブ JSON データ型（バイナリ OSON 形式）・IS JSON との互換性維持
23ai（2024） JSON Relational Duality Views・JSON_TRANSFORM・JSON スキーマ検証強化

IS JSON 制約：JSON カラムを定義する

JSON を格納するカラムは VARCHAR2・CLOB・BLOB（または 21c 以降は JSON 型）で定義し、CHECK (col IS JSON) 制約を付けると不正な JSON の挿入を防げます。

JSON カラムを持つテーブルの作成
-- Oracle 12c〜19c: VARCHAR2/CLOB + IS JSON 制約
CREATE TABLE products (
    product_id   NUMBER         PRIMARY KEY,
    product_name VARCHAR2(200)  NOT NULL,
    attributes   VARCHAR2(4000)             -- JSON を格納する列
        CONSTRAINT chk_attr_json CHECK (attributes IS JSON),
    metadata     CLOB                       -- 大きな JSON は CLOB
        CONSTRAINT chk_meta_json CHECK (metadata IS JSON)
);

-- Oracle 21c 以降: ネイティブ JSON 型（推奨）
-- バイナリ形式（OSON）で格納されるため、パース不要で高速
CREATE TABLE products_21c (
    product_id   NUMBER  PRIMARY KEY,
    product_name VARCHAR2(200),
    attributes   JSON    -- 21c 以降のネイティブ型
);

-- サンプルデータを INSERT
INSERT INTO products VALUES (
    1, 'ノートPC',
    '{"brand":"TechCo","cpu":"Core i7","ram_gb":16,"tags":["laptop","business"],"price":{"jpy":150000,"usd":1000}}'
);
INSERT INTO products VALUES (
    2, 'ワイヤレスマウス',
    '{"brand":"PeriphCo","color":"black","dpi":1600,"tags":["mouse","wireless"],"price":{"jpy":5000,"usd":35}}'
);
COMMIT;

JSON_VALUE：単一のスカラー値を取り出す

JSON_VALUE(json_col, path) は JSON パス式で指定した場所のスカラー値（文字列・数値・真偽値）を返します。オブジェクトや配列を返す場合は後述の JSON_QUERY を使います。

JSON_VALUE の基本と ERROR/NULL/DEFAULT 節
-- JSON_VALUE(列, パス式 [RETURNING 型] [ON ERROR/ON EMPTY 処理])
SELECT
    product_id,
    JSON_VALUE(attributes, '$.brand')           AS brand,
    JSON_VALUE(attributes, '$.ram_gb' RETURNING NUMBER) AS ram_gb,
    JSON_VALUE(attributes, '$.price.jpy' RETURNING NUMBER) AS price_jpy,
    JSON_VALUE(attributes, '$.color')            AS color   -- NULL（存在しない場合）
FROM products;

-- ERROR ON ERROR: パスが見つからない場合に例外を発生させる
-- NULL ON ERROR（デフォルト）: パスが見つからない場合に NULL を返す
-- DEFAULT 'N/A' ON EMPTY: 値が存在しない（空）場合のデフォルト値
SELECT
    JSON_VALUE(attributes, '$.brand'
        ERROR   ON ERROR
        DEFAULT '不明' ON EMPTY
    ) AS brand_safe
FROM products;

-- WHERE 句での絞り込み（JSON_VALUE = 値）
SELECT product_name
FROM products
WHERE JSON_VALUE(attributes, '$.brand') = 'TechCo';
JSON_VALUE のパフォーマンス：関数ベースインデックスが有効
JSON_VALUE を WHERE 句で使う場合、フルスキャンになります。頻繁に検索する属性には関数ベースインデックスを作成すると高速化できます：CREATE INDEX idx_brand ON products (JSON_VALUE(attributes, '$.brand'));

JSON_EXISTS・JSON_QUERY：存在確認とオブジェクト取得

JSON_EXISTS で条件絞り込み・JSON_QUERY でオブジェクト/配列を取得
-- JSON_EXISTS(列, パス): パスが存在する行だけ返す（TRUE/FALSE）
SELECT product_name
FROM products
WHERE JSON_EXISTS(attributes, '$.ram_gb');          -- ram_gb が存在する商品

-- 配列内の特定値の存在チェック（? フィルタ演算子）
SELECT product_name
FROM products
WHERE JSON_EXISTS(attributes, '$.tags[*]?(@ == "business")');
-- tags 配列に "business" を含む商品

-- JSON_QUERY: オブジェクト・配列をそのまま返す（スカラーには使えない）
SELECT
    product_id,
    JSON_QUERY(attributes, '$.price')             AS price_obj,   -- {"jpy":150000,"usd":1000}
    JSON_QUERY(attributes, '$.tags')              AS tags_arr,    -- ["laptop","business"]
    JSON_QUERY(attributes, '$.tags' WITH ARRAY WRAPPER) AS tags_wrapped  -- 常に配列で返す
FROM products;

JSON_TABLE：JSON 配列を行に展開する（最重要）

JSON_TABLE は JSON の配列やネストされたオブジェクトをリレーショナルな表形式に展開します。JSON を集計・JOIN・グループ集計するときに必須の関数です。

JSON_TABLE の基本：配列要素を行に展開
-- JSON_TABLE(json_col, パス COLUMNS(列定義...))
-- products の tags 配列を1行ずつに展開
SELECT
    p.product_id,
    p.product_name,
    t.tag
FROM products p,
     JSON_TABLE(
         p.attributes,
         '$.tags[*]'           -- 配列の全要素を展開
         COLUMNS (
             tag VARCHAR2(100) PATH '$'   -- 各要素の値
         )
     ) t;

-- 結果:
-- PRODUCT_ID  PRODUCT_NAME    TAG
-- 1           ノートPC         laptop
-- 1           ノートPC         business
-- 2           ワイヤレスマウス  mouse
-- 2           ワイヤレスマウス  wireless
JSON_TABLE でネストオブジェクトと配列を同時に展開
-- より複雑な JSON を展開する例
-- [{"order_id":1,"items":[{"sku":"A","qty":2},{"sku":"B","qty":1}]},...]
SELECT
    o.order_id,
    o.order_date,
    i.sku,
    i.qty
FROM orders_json oj,
     JSON_TABLE(
         oj.order_data,
         '$[*]'                        -- トップレベルの配列を展開
         COLUMNS (
             order_id   NUMBER        PATH '$.order_id',
             order_date VARCHAR2(20)  PATH '$.order_date',
             NESTED PATH '$.items[*]'  -- ネストした配列をさらに展開
             COLUMNS (
                 sku VARCHAR2(20) PATH '$.sku',
                 qty NUMBER       PATH '$.qty'
             )
         )
     ) o, JSON_TABLE(...) i   -- 実際は1つのJSON_TABLE内でNESTED PATHを使う
;

-- 正しい書き方（NESTED PATH を使った単一 JSON_TABLE）
SELECT order_id, order_date, sku, qty
FROM orders_json,
     JSON_TABLE(
         order_data, '$[*]'
         COLUMNS (
             order_id   NUMBER       PATH '$.order_id',
             order_date VARCHAR2(20) PATH '$.order_date',
             NESTED PATH '$.items[*]'
             COLUMNS (
                 sku VARCHAR2(20) PATH '$.sku',
                 qty NUMBER       PATH '$.qty'
             )
         )
     );

JSON_OBJECT・JSON_ARRAY・JSON_ARRAYAGG：SQL 結果を JSON で生成する

Oracle 18c 以降では、SELECT 結果を JSON 形式で出力する関数が使えます。REST API のレスポンス生成やデータエクスポートに役立ちます。

JSON_OBJECT・JSON_ARRAY・JSON_ARRAYAGG の使い方
-- JSON_OBJECT: 行を JSON オブジェクトに変換（Oracle 18c 以降）
SELECT
    JSON_OBJECT(
        KEY 'id'    VALUE employee_id,
        KEY 'name'  VALUE last_name,
        KEY 'salary' VALUE salary,
        KEY 'dept'  VALUE department_id
        ABSENT ON NULL    -- NULL 値のキーを省略（デフォルトは NULL 出力）
    ) AS emp_json
FROM employees
WHERE department_id = 50
FETCH FIRST 3 ROWS ONLY;
-- 結果例: {"id":100,"name":"King","salary":800000,"dept":50}

-- JSON_ARRAY: 複数の値を配列にまとめる
SELECT JSON_ARRAY(1, 2, 3, 'hello', NULL ABSENT ON NULL) FROM DUAL;
-- [1,2,3,"hello"]

-- JSON_ARRAYAGG: 複数行を1つの JSON 配列に集約（Oracle 18c 以降）
SELECT
    department_id,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            KEY 'id'   VALUE employee_id,
            KEY 'name' VALUE last_name
        )
        ORDER BY employee_id        -- 配列内の順序を指定
    ) AS employees_json
FROM employees
GROUP BY department_id
ORDER BY department_id;
-- 結果例: department_id=50 → [{"id":100,"name":"Whalen"},{"id":101,"name":"Fay"},...]
JSON_OBJECTAGG：GROUP BY した結果をオブジェクトに集約
-- JSON_OBJECTAGG: キーと値のペアを集約してオブジェクトを生成
SELECT
    JSON_OBJECTAGG(
        KEY   TO_CHAR(department_id)
        VALUE ROUND(AVG(salary))
        ORDER BY department_id
    ) AS dept_avg_salary
FROM employees
GROUP BY ()    -- GROUP BY () = 全体を1行に集約
;
-- 結果例: {"10":4400,"20":6500,"50":3475,...}

Oracle 21c：ネイティブ JSON データ型

Oracle 21c で追加された JSON データ型は、OSON（Oracle binary JSON）形式でバイナリ保存されます。VARCHAR2/CLOB と異なりパース済みで格納されるため、JSON_VALUE などの操作が高速化されます。

JSON データ型（Oracle 21c 以降）
-- 21c 以降: JSON 型で定義（IS JSON 制約は不要）
CREATE TABLE events (
    event_id  NUMBER GENERATED ALWAYS AS IDENTITY,
    event_ts  TIMESTAMP DEFAULT SYSTIMESTAMP,
    payload   JSON    -- ネイティブ JSON 型
);

-- INSERT: 文字列として渡すと自動的にパース・バイナリ変換される
INSERT INTO events (payload)
VALUES ('{"type":"click","user_id":42,"page":"/home","session":"abc123"}');

-- SELECT: JSON_VALUE などは VARCHAR2/CLOB と同じ構文で使える
SELECT
    event_id,
    JSON_VALUE(payload, '$.type')    AS event_type,
    JSON_VALUE(payload, '$.user_id' RETURNING NUMBER) AS user_id
FROM events;

-- 21c では JSON 型に対して直接ドット記法が使える（簡略構文）
SELECT
    event_id,
    e.payload.type.string()     AS event_type,   -- .string() でキャスト
    e.payload.user_id.number()  AS user_id
FROM events e;

Oracle 23ai：JSON Relational Duality Views

Oracle 23ai で追加された JSON Relational Duality Views は、既存のリレーショナルテーブルを JSON ドキュメント形式の API として公開する機能です。SELECT で JSON を取得し、INSERT/UPDATE/DELETE も JSON ドキュメント単位で行えます。バックエンドはリレーショナルなので、既存の SQL クエリや制約はそのまま有効です。

JSON Relational Duality Views の作成と操作（Oracle 23ai）
-- 例: employees テーブルと departments テーブルを JOIN して
-- 部門と所属従業員を1つの JSON ドキュメントとして公開する

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW dept_employees_dv AS
SELECT JSON {
    'dept_id'   : d.department_id,
    'dept_name' : d.department_name,
    'employees' : [
        SELECT JSON {
            'emp_id'  : e.employee_id,
            'name'    : e.last_name,
            'salary'  : e.salary
        }
        FROM employees e
        WHERE e.department_id = d.department_id
    ]
}
FROM departments d;

-- SELECT: JSON ドキュメントとして取得
SELECT data FROM dept_employees_dv WHERE json_value(data, '$.dept_id') = 50;
-- 結果: {"dept_id":50,"dept_name":"Shipping","employees":[{"emp_id":100,...},...]

-- UPDATE: JSON ドキュメントを更新するとリレーショナルテーブルが更新される
-- （リレーショナルとの双方向同期）

まとめ

  • IS JSON 制約（12c〜）でカラムを JSON 専用に保護。21c 以降は JSON 型が推奨
  • JSON_VALUE：スカラー値を取り出す。頻繁に検索する属性には関数ベースインデックスを作成する
  • JSON_EXISTS：特定パス・値の存在を WHERE 条件にする
  • JSON_QUERY：オブジェクト・配列をそのまま取り出す
  • JSON_TABLE：JSON 配列をリレーショナルな行に展開。NESTED PATH で多段配列にも対応
  • JSON_OBJECT / JSON_ARRAYAGG（18c〜）：SQL クエリ結果を JSON に変換。REST API レスポンス生成に便利
  • 21c JSON 型：バイナリ OSON 形式でパース済み格納。ドット記法でアクセス可能
  • 23ai Duality Views：リレーショナルテーブルを JSON API として双方向公開

JSON と組み合わせて使う分析・集計クエリのパターンはWITH句（共通表式）の使い方完全ガイドを、Oracle 23ai の全体的な新機能はOracle 23ai 新機能・変更点完全ガイドも参照してください。