【PL/SQL】JSON_TABLEでJSONを取り込む方法｜NESTED PATH・MERGE・エラー処理まで

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

JSON_TABLE は、Oracle DatabaseのJSONデータをリレーショナルな行・列として展開するSQL/JSON関数です。PL/SQLの取り込みバッチと組み合わせると、APIレスポンスやJSON連携ファイルをステージングし、検証し、正規化テーブルへ MERGE する処理をSQL中心で組み立てられます。

この記事では、JSON機能全体の説明ではなく、PL/SQLでJSONを取り込む実務レシピに絞って解説します。JSON関数全体の整理は Oracle JSON完全ガイド、APIからJSONを取得する処理は UTL_HTTP完全ガイド もあわせて確認してください。

この記事で扱うこと

  • JSON_TABLE でJSONを行・列へ展開する基本
  • NESTED PATH で配列を明細行へ変換する方法
  • ON ERROR / ON EMPTY の使い分け
  • ステージング表、正規化表、MERGE による取り込み
  • DBMS_ERRLOG、再実行、重複排除、性能設計
  • PL/SQL例外処理とログ設計
スポンサーリンク

JSON_TABLEの基本

JSON_TABLE は FROM 句で使い、JSON文書に対して行パスと列定義を指定します。列ごとにJSONパス、SQLデータ型、欠損時や変換エラー時の動作を指定できるため、JSONをSQLで扱う入口として非常に便利です。

行パスJSON_TABLE(payload, '$' ...) の '$' が文書全体を表します。
列定義COLUMNS でJSONキーをSQL列へ投影します。
配列NESTED PATH で配列要素を複数行へ展開します。
エラー制御既定は多くの場面で NULL ON ERROR ですが、重要列は明示します。

ステージング表を用意する

実務では、JSONをいきなり本テーブルへ入れるより、まず受信JSONをそのままステージング表へ保存する設計が安定します。取り込み単位、受信日時、処理状態、エラーメッセージを持たせると、再実行や障害調査が楽になります。

json-stage-table.sql
CREATE TABLE order_json_stage (
  stage_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  source_name   VARCHAR2(100) NOT NULL,
  payload       CLOB CONSTRAINT order_payload_is_json CHECK (payload IS JSON),
  status        VARCHAR2(20) DEFAULT 'READY' NOT NULL,
  error_message VARCHAR2(4000),
  created_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
  processed_at  TIMESTAMP
);

INSERT INTO order_json_stage(source_name, payload)
VALUES('api/orders', '{
  "orderId": "A1001",
  "customer": {"id": 101, "name": "Sato"},
  "orderDate": "2026-05-27T10:15:00",
  "items": [
    {"sku":"P-001","qty":2,"price":1200.5},
    {"sku":"P-002","qty":1,"price":980}
  ],
  "note": null
}');

COMMIT;

JSONをCLOBで持つ場合、サイズや変換処理にも注意します。LOB処理の基本は DBMS_LOB完全ガイド が参考になります。

単層キーを列に展開する

まずは注文ヘッダーのような単層データを列へ展開します。数値や日時に変換する列は、異常値が入ったときにどう扱うかを明示します。

json-table-basic.sql
SELECT s.stage_id,
       j.order_id,
       j.customer_id,
       j.customer_name,
       j.order_ts
FROM order_json_stage s
     CROSS APPLY JSON_TABLE(
       s.payload,
       '$'
       COLUMNS (
         order_id      VARCHAR2(30)  PATH '$.orderId' ERROR ON EMPTY,
         customer_id   NUMBER        PATH '$.customer.id' ERROR ON ERROR,
         customer_name VARCHAR2(100) PATH '$.customer.name' NULL ON EMPTY,
         order_ts      TIMESTAMP     PATH '$.orderDate' NULL ON ERROR
       )
     ) j
WHERE s.status = 'READY';

必須キーは ERROR ON EMPTY、任意項目は NULL ON EMPTY、異常値を落としたい列は NULL ON ERROR など、列の意味に合わせて使い分けます。

NESTED PATHで配列を明細行にする

明細配列は NESTED PATH で展開します。親の注文IDと子の明細項目を同じ JSON_TABLE の中で定義すると、親子の関係が読みやすくなります。

json-table-nested.sql
SELECT s.stage_id,
       j.order_id,
       j.line_no,
       j.sku,
       j.qty,
       j.price
FROM order_json_stage s
     CROSS APPLY JSON_TABLE(
       s.payload,
       '$'
       COLUMNS (
         order_id VARCHAR2(30) PATH '$.orderId' ERROR ON EMPTY,
         NESTED PATH '$.items[*]'
         COLUMNS (
           line_no FOR ORDINALITY,
           sku     VARCHAR2(50) PATH '$.sku' ERROR ON EMPTY,
           qty     NUMBER       PATH '$.qty' DEFAULT 0 ON ERROR,
           price   NUMBER       PATH '$.price' NULL ON ERROR
         )
       )
     ) j
WHERE s.stage_id = 1;

FOR ORDINALITY を使うと、配列内の順序を行番号として取得できます。明細の並び順が業務的に意味を持つ場合は、取り込み先にも行番号を保存しておくと再現性が上がります。

取り込み先テーブルを作る

JSONをそのまま使い続けるより、検索や集計でよく使う項目は正規化テーブルへ落とす方が扱いやすいことが多いです。ここでは注文ヘッダーと明細テーブルへ分けます。

target-tables.sql
CREATE TABLE orders_h (
  order_id      VARCHAR2(30) PRIMARY KEY,
  customer_id   NUMBER NOT NULL,
  customer_name VARCHAR2(100),
  order_ts      TIMESTAMP,
  source_stage_id NUMBER,
  updated_at    TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE orders_d (
  order_id   VARCHAR2(30) NOT NULL,
  line_no    NUMBER NOT NULL,
  sku        VARCHAR2(50) NOT NULL,
  qty        NUMBER DEFAULT 0 NOT NULL,
  price      NUMBER,
  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  CONSTRAINT orders_d_pk PRIMARY KEY (order_id, line_no),
  CONSTRAINT orders_d_fk FOREIGN KEY (order_id) REFERENCES orders_h(order_id)
);

OracleではMERGEでUPSERTする

Oracle SQLでは、他DBのUPSERT構文をそのまま使わず MERGE に置き換えます。JSONから取り込むUPSERTは、基本的に MERGE を使います。MERGEの詳細は Oracle MERGE文完全ガイド も参考になります。

merge-order-header.sql
MERGE INTO orders_h d
USING (
  SELECT s.stage_id,
         j.order_id,
         j.customer_id,
         j.customer_name,
         j.order_ts
  FROM order_json_stage s
       CROSS APPLY JSON_TABLE(
         s.payload,
         '$'
         COLUMNS (
           order_id      VARCHAR2(30)  PATH '$.orderId' ERROR ON EMPTY,
           customer_id   NUMBER        PATH '$.customer.id' ERROR ON ERROR,
           customer_name VARCHAR2(100) PATH '$.customer.name' NULL ON EMPTY,
           order_ts      TIMESTAMP     PATH '$.orderDate' NULL ON ERROR
         )
       ) j
  WHERE s.status = 'READY'
) src
ON (d.order_id = src.order_id)
WHEN MATCHED THEN
  UPDATE SET d.customer_id     = src.customer_id,
             d.customer_name   = src.customer_name,
             d.order_ts        = src.order_ts,
             d.source_stage_id = src.stage_id,
             d.updated_at      = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, customer_name, order_ts, source_stage_id)
  VALUES (src.order_id, src.customer_id, src.customer_name, src.order_ts, src.stage_id);

一意制約違反が出る場合は、入力JSON内に同じキーが重複している可能性があります。ORA-00001 の切り分けやMERGEでの重複対策は ORA-00001の原因と解決方法 も関連します。

明細もMERGEで取り込む

ヘッダーを先にMERGEしたあと、明細をMERGEします。明細では order_id + line_no のようなキーを決め、再実行しても同じ結果になるようにします。

merge-order-detail.sql
MERGE INTO orders_d d
USING (
  SELECT j.order_id,
         j.line_no,
         j.sku,
         j.qty,
         j.price
  FROM order_json_stage s
       CROSS APPLY JSON_TABLE(
         s.payload,
         '$'
         COLUMNS (
           order_id VARCHAR2(30) PATH '$.orderId' ERROR ON EMPTY,
           NESTED PATH '$.items[*]'
           COLUMNS (
             line_no FOR ORDINALITY,
             sku     VARCHAR2(50) PATH '$.sku' ERROR ON EMPTY,
             qty     NUMBER       PATH '$.qty' DEFAULT 0 ON ERROR,
             price   NUMBER       PATH '$.price' NULL ON ERROR
           )
         )
       ) j
  WHERE s.status = 'READY'
) src
ON (d.order_id = src.order_id AND d.line_no = src.line_no)
WHEN MATCHED THEN
  UPDATE SET d.sku        = src.sku,
             d.qty        = src.qty,
             d.price      = src.price,
             d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (order_id, line_no, sku, qty, price)
  VALUES (src.order_id, src.line_no, src.sku, src.qty, src.price);

PL/SQLで取り込み処理をまとめる

実務では、MERGEを1本ずつ手で実行するのではなく、PL/SQLプロシージャとしてまとめます。成功時はステージングを DONE、失敗時は ERROR にして、再実行できるようにします。

import-json-procedure.sql
CREATE OR REPLACE PROCEDURE import_order_json(p_stage_id IN NUMBER)
AS
BEGIN
  MERGE INTO orders_h d
  USING (
    SELECT s.stage_id,
           j.order_id,
           j.customer_id,
           j.customer_name,
           j.order_ts
    FROM order_json_stage s
         CROSS APPLY JSON_TABLE(
           s.payload,
           '$'
           COLUMNS (
             order_id      VARCHAR2(30)  PATH '$.orderId' ERROR ON EMPTY,
             customer_id   NUMBER        PATH '$.customer.id' ERROR ON ERROR,
             customer_name VARCHAR2(100) PATH '$.customer.name' NULL ON EMPTY,
             order_ts      TIMESTAMP     PATH '$.orderDate' NULL ON ERROR
           )
         ) j
    WHERE s.stage_id = p_stage_id
  ) src
  ON (d.order_id = src.order_id)
  WHEN MATCHED THEN
    UPDATE SET d.customer_id = src.customer_id,
               d.customer_name = src.customer_name,
               d.order_ts = src.order_ts,
               d.source_stage_id = src.stage_id,
               d.updated_at = SYSTIMESTAMP
  WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, customer_name, order_ts, source_stage_id)
    VALUES (src.order_id, src.customer_id, src.customer_name, src.order_ts, src.stage_id);

  -- 明細MERGEは別SQLとして実行する
  -- merge-order-detail.sql と同じ考え方で実装

  UPDATE order_json_stage
  SET status = 'DONE',
      processed_at = SYSTIMESTAMP,
      error_message = NULL
  WHERE stage_id = p_stage_id;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;

    UPDATE order_json_stage
    SET status = 'ERROR',
        processed_at = SYSTIMESTAMP,
        error_message = SUBSTR(SQLERRM, 1, 4000)
    WHERE stage_id = p_stage_id;
    COMMIT;

    RAISE;
END;
/

例外をJSON形式で返すAPI設計は 例外をJSON形式で返すREST対応API設計 と相性があります。動的SQLやバインド変数を使う場合は 動的SQLとBIND変数の最適設計 も確認してください。

ON ERRORとON EMPTYの使い分け

JSON取り込みでは、欠損値と型変換エラーを分けて考えます。欠損なら許容するのか、異常値なら落とすのか、既定値を入れるのかを列ごとに決めます。

ERROR ON EMPTY注文IDなど必須キーがない場合に取り込みを止めます。
NULL ON EMPTY備考や任意属性など、存在しなくてもよい列に使います。
ERROR ON ERROR顧客IDなど、型変換に失敗したら不正データとして止めたい列に使います。
NULL ON ERROR欠損や変換失敗を後続チェックで扱う列に使います。
DEFAULT ON ERROR数量の既定値など、業務的に補完してよい列にだけ使います。

DBMS_ERRLOGで行単位エラーを残す

すべてのエラーで処理全体を止めると、1件の不正データで大量取り込みが止まります。行単位で許容できるエラーは DBMS_ERRLOG と LOG ERRORS を検討します。詳しくは DBMS_ERRLOGの使い方 でも扱っています。

dbms-errlog.sql
BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name => 'ORDERS_D',
    err_log_table_name => 'ERR$_ORDERS_D'
  );
END;
/

INSERT INTO orders_d(order_id, line_no, sku, qty, price)
SELECT j.order_id, j.line_no, j.sku, j.qty, j.price
FROM order_json_stage s
     CROSS APPLY JSON_TABLE(
       s.payload,
       '$'
       COLUMNS (
         order_id VARCHAR2(30) PATH '$.orderId' ERROR ON EMPTY,
         NESTED PATH '$.items[*]'
         COLUMNS (
           line_no FOR ORDINALITY,
           sku     VARCHAR2(50) PATH '$.sku' ERROR ON EMPTY,
           qty     NUMBER       PATH '$.qty' NULL ON ERROR,
           price   NUMBER       PATH '$.price' NULL ON ERROR
         )
       )
     ) j
WHERE s.stage_id = 1
LOG ERRORS INTO err$_orders_d ('json import') REJECT LIMIT UNLIMITED;

検索性能とインデックス

JSONを検索する頻度が高い場合は、毎回全文を展開するのではなく、検索キーを仮想列や関数インデックスにします。頻繁に集計するなら、取り込み時に正規化テーブルへ落とす方がシンプルです。

json-index.sql
ALTER TABLE order_json_stage ADD (
  order_id_v GENERATED ALWAYS AS (
    JSON_VALUE(payload, '$.orderId' RETURNING VARCHAR2(30))
  ) VIRTUAL
);

CREATE INDEX order_json_stage_ix1
ON order_json_stage(order_id_v);

SELECT stage_id
FROM order_json_stage
WHERE order_id_v = 'A1001';

JSONを保存したまま検索するのか、正規化して検索するのかは、検索頻度、更新頻度、JSONの変化しやすさで決めます。大量の検索・集計が必要なら、JSON_TABLEは取り込み時に使い、業務検索は通常テーブルに寄せる構成が扱いやすいです。

再実行できる取り込み設計

JSON取り込みバッチは、一度失敗したら終わりではありません。再実行しても二重登録されない、途中まで成功したデータを壊さない、エラーの原因が追える、という設計が大切です。

ステータス管理READY、PROCESSING、DONE、ERROR を持たせます。
冪等性自然キーや外部IDを使い、MERGE で再実行できるようにします。
エラー保存SQLERRMだけでなく、対象stage_idやJSONキーも残します。
重複排除JSON内に同じIDが複数ある場合の優先順位を決めます。
トランザクションヘッダーと明細を同じ単位でコミットするか、分割するかを決めます。

本番前チェックリスト

Oracle構文他DBのUPSERT構文が混ざっていないか確認します。
必須項目ERROR ON EMPTY で欠損を検出する列を決めます。
型変換数値、日時、真偽値、NULLの扱いをサンプルJSONで確認します。
配列NESTED PATH と FOR ORDINALITY で明細順序を保存します。
MERGEON句に一意なキーを使い、重複時の扱いを決めます。
ログステージング状態、エラー、処理件数、再実行履歴を残します。
性能必要列だけ展開し、検索キーは仮想列やインデックスを検討します。

まとめ

JSON_TABLE を使うと、Oracle上のJSONをSQLの行・列として展開できます。PL/SQLでは、受信JSONをステージング表へ保存し、JSON_TABLE で検証・展開し、MERGE で正規化テーブルへ反映する流れが実務向きです。

重要なのは、他DB構文を混ぜずOracleの MERGE を使うこと、ON ERROR / ON EMPTY を列ごとに明示すること、再実行できるステータス管理とログを持つことです。JSON_TABLEは強力ですが、取り込みバッチではデータ品質・再実行・性能まで含めて設計しましょう。