4 JSON列を含む表

JSONデータは、データベース表の列に格納できます。ユースケースが主にドキュメント中心である場合、ドキュメントAPIで最も簡単に使用できるように、文書をJSONコレクション表に格納できます。ただし、JSONデータは同じ方法でアクセス、問合せおよび更新できます。

  • JSON列を含む表の作成
    1つ以上のJSON列含むデータベース表を、単独でまたはリレーショナル列とともに作成できます。JSON列にはJSONデータ型を使用することをお薦めします。
  • 列に必ずJSONデータのみが含まれているかどうかの判別
    表またはビューの指定された列に整形式のJSONデータのみを含めることができるかどうかは、どうすれば確認できるでしょうか。この場合、この列はDBA_JSON_COLUMNS、USER_JSON_COLUMNSおよびALL_JSON_COLUMNSの静的データ・ディクショナリ・ビューにリストされます。

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

4.1 JSON列を含む表の作成

1つ以上のJSON列含むデータベース表を、単独でまたはリレーショナル列とともに作成できます。JSON列にはJSONデータ型を使用することをお薦めします。

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

例4-1、例4-2および例4-3にこれを示します。これらの例では、このマニュアル内の別の場所にある例で使用されるデータを保持する表を作成し、データを入力します。例4-3では、簡潔にするために、2行のデータ(2つの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は戻されません。)

例4-1と例4-2は、JSON型とVARCHAR2をそれぞれ使用して、表を作成する代替の方法です。

JSON型列を定義するとき、型キーワードJSONの後にカッコで囲んだJSON型修飾子((OBJECT)、(ARRAY)または(SCALAR))を指定できます。この場合、列の内容はそれぞれJSONオブジェクト、配列またはスカラー値である必要があります。(これは、単にVARCHAR2ではなくVARCHAR(42)を使用するのと似ています。)

修飾子キーワードSCALARの後には、スカラーの必要な型を指定するキーワードBOOLEAN、BINARY、BINARY_DOUBLE、BINARY_FLOAT、DATE、INTERVAL DAY TO SECOND、 INTERVAL YEAR TO MONTH、NULL、NUMBER、STRING、TIMESTAMPまたはTIMESTAMP WITH TIME ZONEを続けることができます。

カッコの中に、複数の修飾子をカンマで区切って指定することができます。たとえば、(OBJECT, ARRAY)には非スカラー値が必要で、(OBJECT, SCALAR DATE)ではオブジェクトまたは日付のみが許可されます。

指定した修飾子OBJECT、ARRAYおよびSCALAR (スカラー型キーワードが続く可能性あり)の後に、オプションのLIMITキーワード、その後に、そのJSON型列に割り当てる最大バイト数(整数値)を続けて指定できます。列にサイズ制限を指定すると、そのデータをより効率的に処理できるようになります。

指定した修飾子にARRAYが含まれており、その後にキーワードLIMITが続いていない場合は、キーワードARRAYの後に、カッコ(( ))で囲んで、次のような、使用可能な配列値をさらに指定する構文を指定できます。

  1. すべての配列要素に対する(単一)スカラー型。(修飾子SCALARの後に指定可能な、任意の型名を使用できます。)

  2. オプションで、キーワードALLOWまたはDISALLOWの後にキーワードNULL。ALLOWの場合、これは、指定したスカラー型(前述の1)に加えて、任意の配列要素をそれぞれJSON nullにできるということです。このデフォルト動作はDISALLOW NULLです。これは、配列要素をJSON nullにできないということです(指定したスカラー型自体がNULLである場合を除く)。

  3. オプションで、カンマ(,)の後に、アスタリスク(*)、または配列内の要素の最大数を示す整数。アスタリスクを指定した場合や何も指定しなかった場合は、配列サイズ制限なしを意味します。

  4. オプションで、カンマ(,)の後にキーワードSORT。これは、各配列値を、スカラー要素型に従ってその要素を昇順にソートした状態で格納することを意味します。たとえば、文字列値は辞書順でソートされ、数値は数値順でソートされ、日付は(増加)時間順でソートされます。

ノート:

特定の列にJSON型修飾子があるかどうか、またある場合はどの種類の修飾子(OBJECT、ARRAYまたはSCALAR)があるかを確認するには、静的ディクショナリ・ビューALL_TAB_COLUMNS、DBA_TAB_COLUMNS、USER_TAB_COLUMNS、ALL_TAB_COLS、DBA_TAB_COLSおよびUSER_TAB_COLSのいずれかの列JSON_MODIFIERを調べます。『Oracle Databaseリファレンス』の「ALL_TAB_COLUMNS」および「ALL_TAB_COLS」を参照してください。

ノート:

JSON型列のJSONデータは、JSONスキーマのVALIDATEチェック制約を列に適用することで、JSON型修飾子で許可されるものよりもさらに具体的になるように(特定の型の特定のフィールドを持つオブジェクトなど)制限できます。「JSONスキーマを使用したJSONデータの検証」を参照してください。

関連項目:

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

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

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

代替である次の表定義でも同じことを実行していますが、これはさらに、列data内のデータがJSONオブジェクトであることを必要としています。 

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

例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,
   data        VARCHAR2 (23767)
   CONSTRAINT ensure_json CHECK (data is json));

ここで許可されるJSONデータは整形式である必要がありますが、緩慢でも厳密でもかまいません。例5-1も同様の例ですが、整形式の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列を含む表

4.2 列に必ず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の使用の結果である。

  • 列は、SQLファンクションjson_mergepatch、json_scalar、json_serializeまたはjson_transformを使用した結果である。

  • 列はJSONデータ型のコンストラクタJSONが使用された結果である。

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

ノート:

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

関連項目:

ALL_JSON_COLUMNSおよび関連データ・ディクショナリ・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください

親トピック: JSON列を含む表