JSON列を含む表の作成

4 JSON列を含む表の作成

JSON列を持つ表を作成できます。この列にはJSONデータ型を使用することをお薦めします。

テキストのJSONデータを使用して、JSON型の列に対してINSERTまたはUPDATE操作を実行する場合、データはコンストラクタJSONで暗黙的にラップされます。列がVARCHAR2、CLOBまたはBLOBの場合、条件is jsonをチェック制約として使用して、挿入されるデータが(整形式の) JSONデータであることを確認します。

例4-1、例4-2および例4-3にこれを示します。これらの例では、このマニュアル内の別の場所にある例で使用されるデータを保持する表を作成し、データを入力します。例4-1と例4-2は、JSON型とVARCHAR2をそれぞれ使用して、表を作成する代替の方法です。

例4-2では、簡潔にするために、3行のデータ(1つのJSON文書)のみが挿入されています。

注意:

チェック制約を使用すると、データのINSERTおよびUPDATE操作のパフォーマンスが低下することがあります。アプリケーションで特定の列に整形式のJSONデータのみが使用されることが確実である場合、チェック制約を無効にすることを検討できますが、制約は削除しないでください。

注意:

SQL/JSON条件is jsonおよびis not jsonは、NULL以外のSQL値に対してはtrueまたはfalseを戻します。ただし、これらは両方とも、SQLのNULLに対してはunknown (trueでもfalseでもありません)を戻します。チェック制約で使用される場合、SQL NULL値の列への挿入が阻止されることはありません。(ただし、SQLのWHERE句で使用される場合、SQL NULLは戻されません。)

関連項目:

完全表j_purchaseorderの作成の詳細は、外部JSONデータのロードを参照してください。
CREATE TABLEの詳細は、Oracle Database SQL言語リファレンスを参照してください。

例4-1 JSON型列を含む表の作成

この例では、JSONデータ型のpo_document列を持つj_purchaseorder表を作成します。JSONデータはJSON型として格納することをお薦めします。

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document JSON

例4-2 JSONデータが整形式であることを確認するためのチェック制約でのIS JSONの使用

この例では、JSONデータ用のVARCHAR2列を持つj_purchaseorder表を作成します。チェック制約を使用して、列のデータが整形式のJSONデータであることを確認します。JSON以外のデータ型を使用してJSONデータを格納する場合は、このようなチェック制約を必ず使用します。

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document VARCHAR2 (23767)
   CONSTRAINT ensure_json CHECK (po_document is json));

例4-3 JSON列へのJSONデータの挿入

この例では、2行のデータをj_purchaseorder表に挿入します。3番目の列にはJSONデータが含まれています。

3番目の列のデータ型がJSONで(例4-1のように)、この例のようにテキスト・データをその列に挿入した場合、データはJSONコンストラクタで暗黙的にラップされ、JSON型のデータが提供されます。

INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    to_date('30-DEC-2014'),
    '{"PONumber"             : 1600,
      "Reference"            : "ABULL-20140421",
      "Requestor"            : "Alexis Bull",
      "User"                 : "ABULL",
      "CostCenter"           : "A50",
      "ShippingInstructions" :
        {"name"    : "Alexis Bull",
         "Address" : {"street"  : "200 Sporting Green",
                      "city"    : "South San Francisco",
                      "state"   : "CA",
                      "zipCode" : 99236,
                      "country" : "United States of America"},
         "Phone"   : [{"type" : "Office", "number" : "909-555-7307"},
                      {"type" : "Mobile", "number" : "415-555-1234"}]},
      "Special Instructions" : null,
      "AllowPartialShipment" : true,
      "LineItems"            :
        [{"ItemNumber" : 1,
          "Part"       : {"Description" : "One Magic Christmas",
                          "UnitPrice"   : 19.95,
                          "UPCCode"     : 13131092899},
          "Quantity"   : 9.0},
         {"ItemNumber" : 2,
          "Part"       : {"Description" : "Lethal Weapon",
                          "UnitPrice"   : 19.95,
                          "UPCCode"     : 85391628927},
          "Quantity"   : 5.0}]}');

INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    to_date('30-DEC-2014'),
    '{"PONumber"             : 672,
      "Reference"            : "SBELL-20141017",
      "Requestor"            : "Sarah Bell",
      "User"                 : "SBELL",
      "CostCenter"           : "A50",
      "ShippingInstructions" : {"name"    : "Sarah Bell",
                                "Address" : {"street"  : "200 Sporting Green",
                                             "city"    : "South San Francisco",
                                             "state"   : "CA",
                                             "zipCode" : 99236,
                                             "country" : "United States of America"},
                                "Phone"   : "983-555-6509"},
      "Special Instructions" : "Courier",
      "LineItems"            :
        [{"ItemNumber" : 1,
          "Part"       : {"Description" : "Making the Grade",
                          "UnitPrice"   : 20,
                          "UPCCode"     : 27616867759},
          "Quantity"   : 8.0},
         {"ItemNumber" : 2,
          "Part"       : {"Description" : "Nixon",
                          "UnitPrice"   : 19.95,
                          "UPCCode"     : 717951002396},
          "Quantity"   : 5},
         {"ItemNumber" : 3,
          "Part"       : {"Description" : "Eric Clapton: Best Of 1981-1999",
                          "UnitPrice"   : 19.95,
                          "UPCCode"     : 75993851120},
          "Quantity"   : 5.0}]}');

列に必ずJSONデータのみが含まれているかどうかの判別
表またはビューの指定された列に整形式のJSONデータのみを含めることができるかどうかは、どうすれば確認できるでしょうか。この場合、この列はDBA_JSON_COLUMNS、USER_JSON_COLUMNSおよびALL_JSON_COLUMNSの静的データ・ディクショナリ・ビューにリストされます。

親トピック: JSONデータの格納および管理

4.1 列に必ずJSONデータのみが含まれているかどうかの判別

表またはビューの指定された列に整形式のJSONデータのみを含めることができるかどうかは、どうすれば確認できるでしょうか。この場合、この列はDBA_JSON_COLUMNS、USER_JSON_COLUMNSおよびALL_JSON_COLUMNSの静的データ・ディクショナリ・ビューにリストされます。

これらの各ビューには、列名、データ型および形式(TEXTまたはBINARY)、表名またはビュー名(列TABLE_NAME)、およびオブジェクトが表かそれともビューか(列OBJECT_TYPE)がリストされます。

JSONデータ型の列には常に整形式のJSONデータのみが含まれているため、そのような各列は常にJSON型としてリストされます。

JSON型以外の列をJSONデータとみなす場合は、その列にis jsonチェック制約が必要となります。ただし、ビューの場合、次の基準のいずれかを満たせば、列はJSONデータとしてみなされます。

基礎となるデータのデータ型がJSONである。
基礎となるデータにis jsonチェック制約が含まれる。
列が、json_objectといったSQL/JSON生成ファンクションの使用の結果である。
列が、SQL/JSONファンクションjson_queryの使用の結果である。
列は、Oracle SQLファンクションjson_mergepatch、json_scalar、json_serializeまたはjson_transformを使用した結果である。
列はJSONデータ型のコンストラクタJSONが使用された結果である。
列はSQLファンクションtreatとキーワードAS JSONの使用で発生したものです。

JSONデータのみが含まれるように表の列を制約するis jsonチェック制約が後で非アクティブ化された場合、その列はビューにリストされたままになります。チェック制約が削除されている場合、列はビューから削除されます。

注意:

チェック制約によって論理条件ORを使用して条件is jsonが別の条件と結合される場合、列はこれらのビューにリストされません。この場合、列内のデータがJSONデータであるかどうかは確実ではありません。たとえば、制約jcol is json OR length(jcol) < 1000の場合、jcol列にJSONデータのみが含まれていることは確認されません。