4 JSON列を含む表

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

  • JSON列を含む表の作成
    1つ以上のJSON列含むデータベース表を、単独でまたはリレーショナル列とともに作成できます。JSON列には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型列のデータの制限を参照してください。

関連項目:

例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);

例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型の列仕様に修飾子キーワードを追加すれば、列サイズをバイト単位で指定でき、列値をオブジェクト、配列、スカラーまたは組合せのみに制限できます。

スカラー値は、日付などの特定のタイプ、または日付や文字列などの複数のスカラー・タイプに制限できます。JSON配列は、常にソートし、特定の数の要素に制限して、特定のスカラー型の要素を設定するように制限できます。

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

JSON型の列のサイズを制限するには、(型修飾子OBJECT、ARRAYおよびSCALARの有無にかかわらず)列指定の後に、オプションのLIMIT修飾子、および列に割り当てる最大バイト数(正の整数)を指定します。列にサイズ制限を指定すると、そのデータをより効率的に処理できるようになります。(これは、単にVARCHAR2を使用するのではなく、VARCHAR2(42)やVARCHAR2(500)を使用するのと似ています。)

特定のユースケース:

  • 修飾子OBJECTが、おそらくJSONデータの最も一般的なユースケースです。Oracle JSONコレクションでは、この修飾子が隠れて使用されています。たとえば、ドキュメント・データベースには通常、JSONオブジェクトであるドキュメントのみが格納されます。

  • SCALAR修飾子が便利な場合があります。スカラー型の組合せ(DATEやVARCHAR2など)で、タイプは異なるが、意味的に同等または類似のデータを許可できます。

  • JSON列値のサイズ制限がアプリケーションで必要または期待されている場合は、列サイズを制限すると、より効率的に処理できます。

例4-4 JSON型のオブジェクト列を含む表の作成

この表の定義は、修飾子OBJECTを使用して、data列のデータがJSONオブジェクトであることを必須にしていること以外は、例4-1と同じことをします。(オブジェクト以外のJSON値を挿入しようとするとエラーになります。) 

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

例4-5 サイズが制限されたJSON型のオブジェクト列を含む表の作成

例4-4と同様に、この例では、data列にJSONオブジェクトが含まれている必要があります。さらに、このような各オブジェクトのバイナリ(OSON形式)のサイズが500バイトに制限されています。修飾子OBJECTがここにないと、すべての型のJSON値を格納でき、各値が最大500バイトになります。 

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

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

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

例4-6 オブジェクトまたは日付のJSON型列を含む表の作成

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

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

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

  2. オプションで、キーワードALLOWまたはDISALLOWの後にキーワードNULL。ALLOWの場合は、指定したスカラー型(前述の1)に加えて、任意の配列要素をJSON nullにできるという意味になります。

    デフォルトの動作はDISALLOW NULLです。これは、配列要素にJSON nullを使用できないという意味になります。

  3. オプション脚注1で、カンマ(,)の後に、アスタリスク(*)、または配列内の要素の最大数を示す正の整数。アスタリスクを指定した場合や何も指定しなかった場合は、配列サイズ制限なしを意味します。(デフォルトでは、アスタリスクが暗黙的に指定されています。)

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

    配列をソートすると、フィルタ条件inを使用したSQL/JSON条件json_existsの使用(JSON値が指定されたリストのメンバーかどうかをチェックする)など、一部の操作でパフォーマンスがよくなる可能性があります。

例4-7 ソートされた数値またはNULLの配列のJSON型列を含む表の作成

この例では、列dataに数値の配列またはJSON null値が含まれている必要があります。配列要素は正規の順序の昇順で格納されます。つまり、null値がすべての数値の前になり、数値が昇順でソートされます。脚注2

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   data        JSON (ARRAY (NUMBER, *, 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データの検証」を参照してください。

関連項目:

  • CREATE TABLEの詳細は、『Oracle Database SQL言語リファレンス』のJSON記憶域句を参照してください

  • JSON型修飾子の構文の詳細は、『Oracle Database SQL言語リファレンス』の「JSONデータ型」および「IS JSON条件」を参照してください

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

4.3 列に必ず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列を含む表


脚注の凡例

脚注1: キーワードSORTが使用されている場合、これはオプションではありません。
脚注2: null値をデフォルトで許容することを明示的に示すには、NUMBERの後にALLOW NULLを含めることができます。