【PL/SQL】JSON_TABLEとPL/SQLを組み合わせたJSON処理

【PL/SQL】JSON_TABLEとPL/SQLを組み合わせたJSON処理 PL/SQL

JSONをRDBの力で高速に扱いたいとき、OracleのJSON_TABLEは最強の相棒になります。JSON列やCLOBに入っているドキュメントを、その場でリレーショナルな行・列に展開できるため、JOINや集計、インデックスの恩恵をフルに受けられます。ここではPL/SQLとの連携を前提に、JSON_TABLEの基本から多段NESTED、エラー処理、型変換、更新・マージの実装パターン、インデックスと性能の勘所までを一気に整理します。

前提と最小構成

まずはJSONを格納するテーブルを用意します。IS JSON制約を付けると不正なJSONの混入を防げます。CLOBでもBLOBでも扱えますが、学習用にCLOBで始めると分かりやすいはずです。

-- 受注明細をJSONで受ける例
CREATE TABLE orders_raw (
  id        NUMBER PRIMARY KEY,
  payload   CLOB CONSTRAINT payload_is_json CHECK (payload IS JSON)
);

-- サンプルデータ（1件に複数アイテム）
INSERT INTO orders_raw(id, payload) VALUES(1, '{
  "orderId": "A1001",
  "customer": {"id": 101, "name": "Sato"},
  "orderDate": "2025-09-30T12:34:56Z",
  "items": [
    {"sku":"P-001","qty":2,"price":1200.5},
    {"sku":"P-002","qty":1,"price":980}
  ],
  "note": null
}');
COMMIT;

基本の展開：単層のキーを列にする

JSON_TABLEはFROM句に置き、PATHでJSONパスを指定し、COLUMNSで列定義を与えます。データ型も同時に定義でき、変換に失敗した際の動作をON ERRORで制御します。

-- 単票情報だけを行化
SELECT t.id, jt.order_id, jt.cust_id, jt.cust_name, jt.order_ts
FROM orders_raw t,
     JSON_TABLE(
       t.payload, '$'
       COLUMNS (
         order_id  VARCHAR2(20)  PATH '$.orderId',
         cust_id   NUMBER        PATH '$.customer.id',
         cust_name VARCHAR2(50)  PATH '$.customer.name',
         order_ts  TIMESTAMP     PATH '$.orderDate' ERROR ON ERROR
       )
     ) jt
WHERE t.id = 1;

ERROR ON ERRORは変換不能時に例外を投げます。運用ではNULL ON ERRORやDEFAULT ON ERRORで堅牢にする選択も現実的です。

配列の展開：NESTED PATHで明細を行にする

明細配列はNESTED PATHを使って“子行”を生成します。親の値と子の値を同時に列化できるため、1回のスキャンで正規化テーブルへ流し込む下準備が整います。

-- 親（注文）＋子（アイテム）を同時展開
SELECT t.id,
       h.order_id, h.order_ts,
       d.sku, d.qty, d.price
FROM orders_raw t,
     JSON_TABLE(
       t.payload, '$'
       COLUMNS (
         order_id  VARCHAR2(20)  PATH '$.orderId',
         order_ts  TIMESTAMP     PATH '$.orderDate',
         NESTED PATH '$.items[*]'
         COLUMNS (
           sku   VARCHAR2(20) PATH '$.sku',
           qty   NUMBER       PATH '$.qty',
           price NUMBER       PATH '$.price'
         )
       )
     ) d
     , JSON_TABLE(t.payload, '$' COLUMNS (order_id VARCHAR2(20) PATH '$.orderId', order_ts TIMESTAMP PATH '$.orderDate')) h
WHERE t.id = 1;

Oracleでは同一JSON_TABLE内の親列とNESTED列を一緒に定義する形が素直ですが、可読性の都合で親と子を分ける構成もよく使われます。

正規化テーブルへの取り込み：INSERT…SELECTで一気に登録

展開結果はそのままINSERTできます。PL/SQLでFORALLを使う必要はなく、集合志向の1文で済ませるのが定石です。

-- 親テーブル
CREATE TABLE orders_h (
  id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id  VARCHAR2(20) UNIQUE,
  order_ts  TIMESTAMP,
  cust_id   NUMBER,
  cust_name VARCHAR2(50)
);

-- 子テーブル
CREATE TABLE orders_d (
  order_id  VARCHAR2(20),
  line_no   NUMBER,
  sku       VARCHAR2(20),
  qty       NUMBER,
  price     NUMBER,
  CONSTRAINT orders_d_fk FOREIGN KEY(order_id) REFERENCES orders_h(order_id)
);

-- 取り込み（親）
INSERT INTO orders_h(order_id, order_ts, cust_id, cust_name)
SELECT j.order_id, j.order_ts, j.cust_id, j.cust_name
FROM orders_raw t,
     JSON_TABLE(t.payload, '$'
       COLUMNS (
         order_id  VARCHAR2(20) PATH '$.orderId',
         order_ts  TIMESTAMP     PATH '$.orderDate' NULL ON ERROR,
         cust_id   NUMBER        PATH '$.customer.id',
         cust_name VARCHAR2(50)  PATH '$.customer.name'
       )
     ) j
ON CONFLICT DO NOTHING; -- 12c以前はMERGEで重複回避

-- 取り込み（子）
INSERT INTO orders_d(order_id, line_no, sku, qty, price)
SELECT j.order_id, ROWNUM AS line_no, j.sku, j.qty, j.price
FROM orders_raw t,
     JSON_TABLE(t.payload, '$'
       COLUMNS (
         order_id VARCHAR2(20) PATH '$.orderId',
         NESTED PATH '$.items[*]'
         COLUMNS (
           sku   VARCHAR2(20) PATH '$.sku',
           qty   NUMBER       PATH '$.qty',
           price NUMBER       PATH '$.price'
         )
       )
     ) j;
COMMIT;

PL/SQLとの連携：バインド値でフィルタし、例外を安全に扱う

PL/SQL側の変数を使ってJSON_TABLE結果を絞り込むのも簡単です。変換エラーは例外節で拾い、入力不備をログへ逃す構成が安定します。

CREATE OR REPLACE PROCEDURE import_orders(p_from TIMESTAMP, p_to TIMESTAMP) IS
  v_count NUMBER := 0;
BEGIN
  INSERT INTO orders_h(order_id, order_ts, cust_id, cust_name)
  SELECT j.order_id, j.order_ts, j.cust_id, j.cust_name
  FROM orders_raw t,
       JSON_TABLE(t.payload, '$'
         COLUMNS (
           order_id  VARCHAR2(20) PATH '$.orderId',
           order_ts  TIMESTAMP     PATH '$.orderDate' NULL ON ERROR,
           cust_id   NUMBER        PATH '$.customer.id',
           cust_name VARCHAR2(50)  PATH '$.customer.name'
         )
       ) j
  WHERE j.order_ts BETWEEN p_from AND p_to;
  v_count := SQL%ROWCOUNT;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('imported='||v_count);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

更新・マージ：JSONからUPSERTする現実解

UPSERT要件はMERGEが第一候補です。JSON_TABLEをUSING側に置くと、JSONのキーを自然にマッチングへ使えます。

MERGE INTO orders_h d
USING (
  SELECT j.order_id, j.order_ts, j.cust_id, j.cust_name
  FROM orders_raw t,
       JSON_TABLE(t.payload, '$'
         COLUMNS (
           order_id  VARCHAR2(20) PATH '$.orderId',
           order_ts  TIMESTAMP     PATH '$.orderDate',
           cust_id   NUMBER        PATH '$.customer.id',
           cust_name VARCHAR2(50)  PATH '$.customer.name'
         )
       ) j
) s
ON (d.order_id = s.order_id)
WHEN MATCHED THEN UPDATE SET d.order_ts=s.order_ts, d.cust_id=s.cust_id, d.cust_name=s.cust_name
WHEN NOT MATCHED THEN INSERT (order_id, order_ts, cust_id, cust_name)
VALUES (s.order_id, s.order_ts, s.cust_id, s.cust_name);

型変換と欠損値：ON ERROR／DEFAULTの使い分け

実データは完全ではありません。日時や数値の欠損・異常時にNULLに落とすのか、既定値を当てるのか、エラーで落とすのかを列単位で決めるのが要点です。

JSON_TABLE(t.payload, '$'
  COLUMNS (
    order_ts TIMESTAMP PATH '$.orderDate' NULL ON ERROR,
    qty      NUMBER    PATH '$.items[0].qty' DEFAULT 0 ON ERROR,
    note     VARCHAR2(200) PATH '$.note' NULL ON EMPTY
  )
)

多段NESTEDと親キーの継承

ネストが深い場合でもNESTEDを重ねれば素直に展開できます。親の列はPATH ‘$’ から拾うか、上位NESTEDで列化した値を再利用します。

SELECT j.order_id, j.sku, j.attr_name, j.attr_value
FROM orders_raw t,
     JSON_TABLE(t.payload, '$'
       COLUMNS (
         order_id VARCHAR2(20) PATH '$.orderId',
         NESTED PATH '$.items[*]'
         COLUMNS (
           sku VARCHAR2(20) PATH '$.sku',
           NESTED PATH '$.attributes[*]'
           COLUMNS (
             attr_name  VARCHAR2(30) PATH '$.name',
             attr_value VARCHAR2(100) PATH '$.value'
           )
         )
       )
     ) j;

インデックスと性能の勘所

検索頻度の高いキーは生成列＋B-tree、あるいは関数インデックスで引けるようにしておくと計画が安定します。JSON_VALUEやJSON_QUERYを使った関数インデックスは扱いやすく、取り込み後に正規化テーブルへ落とす二段構えが現実的です。

-- 生成列＋インデックス
ALTER TABLE orders_raw ADD (
  order_id_v GENERATED ALWAYS AS (JSON_VALUE(payload, '$.orderId')) VIRTUAL
);
CREATE INDEX orders_raw_ix1 ON orders_raw(order_id_v);

-- 直接検索
SELECT id FROM orders_raw
 WHERE JSON_VALUE(payload, '$.customer.id') = 101;

大量展開ではWITH句で重い前処理を一度だけ評価させる、必要列だけを列化する、日時や数値の型変換を極力シンプルに保つ、といった基本を守るだけでI/Oが目に見えて減ります。

検証とデバッグのコツ

まずはJSON_VALUEで単一キーを1つずつ確認し、次にJSON_TABLEで段階的に列を増やすと原因切り分けが容易です。期待した列がNULLになる場合はパス記法、配列インデックス、データ型、ON ERROR/ON EMPTYの指定を見直します。PL/SQLの単体テストではサンプルJSONを一定数ストックし、境界ケースを網羅できるようにしておくと回帰に強くなります。

まとめ

JSON_TABLEは「JSONをその場で表に変える」強力な橋渡しです。単層の列化から配列の行展開、NESTEDの多段化までを1文で表現でき、INSERTやMERGEに直結できます。PL/SQLからは集合志向で呼び出し、例外時のON ERRORと既定値の方針を決め、必要列だけを取り出すのが安定運用の近道です。インデックスと生成列で検索性を補強しつつ、取り込み後は正規化テーブルへ落としてRDBの強みを活かす——この型を押さえれば、JSONとPL/SQLの共存は驚くほどシンプルになります。