Oracle DatabaseのJSON

2 Oracle DatabaseのJSON

Oracle Databaseは、トランザクション、索引付け、宣言的問合せおよびビューを含むリレーショナル・データベースの機能を使用してJSONをネイティブにサポートしています。

このマニュアルでは、Oracle Databaseに格納されたJSONデータを扱うためのデータベース言語および機能の使用について取り上げます。特に、SQLおよびPL/SQLとJSONデータの連携方法について説明します。

注意:

Oracleでは、データベース内に格納されたJSONデータへのアクセス用に、Simple Oracle Document Access (SODA) APIファミリも提供しています。SODAは、リレーショナル・データベース機能やSQLおよびPL/SQLなどの言語に関する知識を使用しないスキーマレス・アプリケーション開発向けに設計されています。データベースで文書がどのように格納されているかを把握しなくても、文書コレクションのOracle Databaseでの作成および格納や、それらの文書の取得および問合せの実行を可能にします。

次のように、いくつかの種類のSODAの実装があります。

SODA for REST: Representational state transfer (REST)のリクエストでは、HTTPコールの実行に対応した任意の言語を使用して、コレクション操作と文書操作が実行されます。
SODA for Java: Javaのクラスおよびインタフェースは、データベース、コレクションおよび文書を表します。
SODA for PL/SQL: PL/SQLのオブジェクト型は、コレクションおよび文書を表します。
SODA for C: Oracle Call Interface (OCI)のハンドルは、コレクションおよび文書を表します。
SODA for Node.js: Node.jsのクラスは、コレクションおよび文書を表します。
SODA for Python: Pythonのオブジェクトは、コレクションおよび文書を表します。

SODAの詳細は、Simple Oracle Document Access (SODA)を参照してください。

Oracle DatabaseでJSONを使用する前に
通常、Oracle DatabaseでJSONデータを使用する際には、(1) JSONデータ型の列を持つ表の作成、(2)列へのJSONデータの挿入、(3)列のデータの問合せの各操作を行います。
Oracle DatabaseにおけるJSONの概要
Oracle Databaseは、トランザクション、索引付け、宣言的問合せおよびビューを含むリレーショナル・データベースの機能を使用してJSONをネイティブにサポートしています。リレーショナル・データとは異なり、データを定義するスキーマを必要とせずに、JSONデータをデータベース内に格納したり、索引付けおよび問合せを行うことができます。
JSONデータ型(他のSQLデータ型との変換)
SQLデータ型JSONとは、ネイティブ・バイナリ形式OSONを使用したJSONデータを意味します。OSONは、Oracle DatabaseサーバーとOracle Databaseクライアントの両方で問合せおよび更新を迅速に行うための、オラクル社による最適化された形式です。他のSQLデータからJSON型インスタンスを作成できます(およびその逆に作成できます)。
JSONに対するOracle Databaseのサポート
JavaScript Object Notation (JSON)に対するOracle Databaseのサポートは、リレーショナル記憶域の使用範囲とJSONデータの問合せの使用範囲のベスト・フィットを実現することにより、リレーショナル問合せとJSON問合せを互いに連携して機能させることを目指しています。Oracle SQL/JSONサポートは、SQL標準のJSONサポートと密接に連携しています。

親トピック: JSONデータおよびOracle Database

2.1 Oracle DatabaseでJSONを使用する前に

通常、Oracle DatabaseでJSONデータを使用する際には、(1) JSONデータ型の列を持つ表の作成、(2)列へのJSONデータの挿入、(3)列のデータの問合せの各操作を行います。

主キー列およびJSONデータ型の列を持つ表を作成します。
次の文では、主キーidとJSON列po_documentがある表j_purchaseorderを作成しています。
```
CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document JSON);
```
または、VARCHAR2などのJSON型以外のデータ型を持つ列にJSONデータを挿入できます。その場合は、is jsonチェック制約を使用して、列に挿入されるデータが整形式のJSONデータであるようにします。例4-2を参照してください。

Oracle Databaseで使用可能な任意のメソッドを使用してJSON列にJSONデータを挿入します。

次の文では、SQL INSERT文を使用して、いくつかの単純なJSONデータを表j_purchaseorderの3つ目の列(これが列po_document。前を参照)に挿入します。ここでは、一部の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" : {...},
            "Special Instructions" : null,
            "AllowPartialShipment" : true,
            "LineItems"            : [...]}');

SQL文字列'{"PONumber":1600,…}'は、INSERT操作のためにJSONデータ型に自動的に変換されます。

JSONデータを問い合せます。戻り値は常に、JSON値を表すVARCHAR2インスタンスです。ここでは、単純な例をいくつか示します。
次の問合せでは、JSON列po_document内の各文書から、スカラー値(JSON列po_document内のオブジェクトのフィールドPONumberの値であるJSON数値)を抽出しています(例13-1も参照)。
```
SELECT po.po_document.PONumber FROM j_purchaseorder po;
```
次の問合せでは、各文書から、JSON電話オブジェクトの配列(フィールドShippingInstructionsの値のオブジェクトのフィールドPhoneの値)を抽出しています(例13-2も参照)。
```
SELECT po.po_document.ShippingInstructions.Phone
  FROM j_purchaseorder po;
```
次の問合せでは、各文書から、複数の値(配列Phone内の各オブジェクトのフィールドtypeの値)を配列として抽出しています。戻される配列は、格納されているデータの一部ではありませんが、問合せによって自動的に構成されます。(配列要素の順序は指定されません。)
```
SELECT po.po_document.ShippingInstructions.Phone.type
  FROM j_purchaseorder po;
```

関連項目

親トピック: Oracle DatabaseのJSON

2.2 Oracle DatabaseにおけるJSONの概要

Oracle Databaseは、トランザクション、索引付け、宣言的問合せおよびビューを含むリレーショナル・データベースの機能を使用してJSONをネイティブにサポートしています。リレーショナル・データとは異なり、データを定義するスキーマを必要とせずに、JSONデータをデータベース内に格納したり、索引付けおよび問合せを行うことができます。

(JSONデータは、それを格納する表および列を定義するためにデータベース・スキーマが使用されている場合であっても、スキーマレスとなります。そのスキーマにはJSONデータ自体の構造は指定されていません。)

多くの場合、JSONデータは、Oracle NoSQL DatabaseおよびOracle Berkeley DBなどのNoSQLデータベースに格納されています。これらを使用すると、スキーマに基づいていないデータを格納および取得できますが、これらにはリレーショナル・データベースの厳密な整合性モデルは用意されていません。

このような短所を補うために、場合によっては、NoSQLデータベースと並行してリレーショナル・データベースが使用されます。したがって、NoSQLデータベースに格納されたJSONデータを使用するアプリケーションでは、データの整合性をそれ自体で確保する必要があります。

JSONがOracle Databaseでネイティブにサポートされることで、このような負荷を未然に防ぐことができます。トランザクション、索引付け、宣言的問合せ、ビューなどの、JSONで使用するためのリレーショナル・データベース機能のメリットすべてを利用できます。

構造化問合せ言語(SQL)を使用したデータベース問合せは宣言的です。Oracle Databaseでは、SQLを使用してJSONデータをリレーショナル・データに結合できます。また、JSONデータをリレーショナルに投影できるため、JSONデータはリレーショナルなプロセスおよびツールで使用できるようになります。さらに、Oracle Databaseの外部で外部表に格納されたJSONデータをデータベース内で問い合せることもできます。

データベースに格納されたJSONデータには、Oracle Call Interface (OCI)、Java Database Connectivity (JDBC)などの、他のデータベース・データへのアクセスと同じ方法でアクセスできます。

JSONデータのデータ型
SQLデータ型JSONは、高速問合せおよび更新のためのOracleのバイナリJSON形式です。標準のJSONスカラー型(数値、文字列、ブールおよびnull)が拡張され、SQLスカラー型に対応する型が追加されます。これにより、JSONとSQLの間のスカラー・データの変換が単純で無損失になります。
データベース表のJSON列
Oracle Databaseでは、JSON文書の格納に使用される表に制限はありません。JSON文書が含まれる列は、他のどんな種類のデータベース・データとも共存させることができます。1つの表に、JSON文書が含まれる複数の列を含めることも可能です。
JSONデータでのSQLの使用
SQLでは、JSONデータ型のコンストラクタJSON、特化された関数と条件、または単純なドット表記を使用して、OracleデータベースにJSONデータを作成したり、アクセスできます。ほとんどのSQLファンクションおよび条件はSQL/JSONの標準に属していますが、一部はOracleに固有のものです。
JSONデータでのPL/SQLの使用
JSONデータ型インスタンスはPL/SQLサブプログラムの入力および出力として使用でき、JSONにSQLコードまたはPL/SQLオブジェクト型を使用して、PL/SQLコード内でJSONデータを操作できます。

関連項目

親トピック: Oracle DatabaseのJSON

2.2.1 JSONデータのデータ型

SQLデータ型JSONは、高速問合せおよび更新のためのOracleのバイナリJSON形式です。標準のJSONスカラー型(数値、文字列、ブールおよびnull)が拡張され、SQLスカラー型に対応する型が追加されます。これにより、JSONとSQLの間のスカラー・データの変換が単純で無損失になります。

標準JSONは、言語または表記法として、オブジェクト、配列、数値、文字列、ブールおよびnullのデータ型が事前定義されています。オブジェクトと配列を除くすべてのJSON言語型はスカラー型です。

標準では、JSONデータはテキスト形式で定義されます。標準構文ではUnicode文字で構成されます。

実際のJSONデータがプログラミング言語で使用されているか、なんらかの方法で格納されている場合、その特定の言語または記憶域形式のデータ型を使用して実現されます。たとえば、JDBCクライアント・アプリケーションでJSONデータを使用してJava文字列を作成したり、SQLデータ型を使用してデータベース列にJSONデータを格納する場合があります。

これらの2種類のデータ型に留意することが重要です。たとえば、JSON値"abc"のJSON言語型は文字列ですが、いくつかのSQLデータ型(JSON、VARCHAR2、CLOBまたはBLOB)の値を使用して、この値を表現または実現できます。

SQL型JSONは、特にJSONデータ用に設計されています。Oracle Databaseで使用する場合は、JSONデータにJSON型を使用することをお薦めします。これにはバイナリ形式OSONが使用されます。これは、Oracle DatabaseサーバーとOracle Databaseクライアントの両方で問合せおよび更新を迅速に行うための、オラクル社による最適化されたバイナリJSON形式です。JSON型は、データベース初期化パラメータcompatibleが少なくとも20である場合にのみ使用できます。

JSONデータに対して他のSQL型(VARCHAR2、CLOBまたはBLOB)のいずれかを使用する場合、そのデータはテキストと呼ばれ、これは解析されていない文字データです(BLOBインスタンスとして格納されている場合でも)。

JSONデータがSQLデータ型JSONの場合、Oracleでは標準のJSON言語のスカラー型(数値、文字列、ブールおよびnull)のセットが拡張され、SQLスカラー型に対応する型が含まれています(binary、date、timestamp、year-month interval、day-second interval、double、float)。これにより、JSON言語が拡張され、JSON言語とSQLの間でスカラー・データの変換が簡単になり、データ損失がなくなります。

JSONデータのSQLデータ型がVARCHAR2、CLOBまたはBLOBの場合は、標準JSON言語のスカラー型のみがサポートされます。ただし、JSONデータのSQL型がJSONの場合、Oracle Databaseでは標準のJSON言語型のセットが拡張されており、SQLスカラー・データ型に直接対応する複数の次のようなスカラー型が含まれています。

binary: SQLのRAWに対応しています。
date: SQLのDATEに対応しています。
timestamp: SQLのTIMESTAMPに対応しています。
year-month interval: SQLのINTERVAL YEAR TO MONTHに対応しています。
day-second interval: SQLのINTERVAL DAY TO SECONDに対応しています。
double: SQLのBINARY_DOUBLEに対応しています。
float: SQLのBINARY_FLOATに対応しています。

JSON型として格納されているJSONデータのそのようなOracle固有のJSON言語型のJSONスカラー値を取得するには、次のいくつかの方法があります。

RETURNING JSONを指定してSQL/JSON生成関数を使用します。配列の要素またはオブジェクトのフィールド値の生成に使用されるスカラーSQL値は、対応するJSON型のJSONスカラー値になります。たとえば、BINARY_FLOATのSQL値はfloatのJSON値になります。
Oracle SQLファンクションjson_scalarを使用します。たとえば、BINARY_FLOATのSQL値に適用すると、floatのJSON値になります。
クライアント側のエンコーディングを持つデータベース・クライアントを使用し、Oracle固有のJSON値をJSON型として作成してから、データベースに送信します。
Oracle固有のJSONスカラー型を持つJSONデータを使用して、JSONのPL/SQLオブジェクト型をインスタンス化します。これには、既存のそのようなオブジェクト型インスタンスの更新も含まれます。
PL/SQL DOMインスタンス(JSON_ELEMENT_Tインスタンス)ではPL/SQLメソッドto_json()を使用します。

Oracle固有のJSON言語型のJSONスカラー値を使用するには、次のいくつかの方法があります。

SQL/JSON条件json_existsを使用して、SQLバインド変数の値を、Oracle固有のJSONスカラー型に対応する項目メソッドを適用した結果と比較します。
Oracle固有のJSONスカラー型に対応するSQL型を返す、RETURNING句を指定したSQL/JSONファンクションjson_valueを使用します。

親トピック: Oracle DatabaseにおけるJSONの概要

2.2.2 データベース表のJSON列

Oracle Databaseでは、JSON文書の格納に使用される表に制限はありません。JSON文書が含まれる列は、他のどんな種類のデータベース・データとも共存させることができます。1つの表に、JSON文書が含まれる複数の列を含めることも可能です。

Oracle DatabaseをJSONのドキュメント・ストアとして使用する場合、JSON列が含まれる表には、通常、JSON以外のハウスキーピング列がいくつか含められます。これらは、通常JSON文書に関するメタデータをトラッキングします。

JSONデータを使用して、主にリレーショナル・アプリケーションに柔軟性を追加する場合、いくつかの表にはJSON文書用の列も作成されることが考えられます。これらの列は、リレーショナル・モデルに直接マッピングされないアプリケーション・データの管理のために使用されます。

JSON列にはJSONデータ型を使用することをお薦めします。かわりにテキストのJSON記憶域(VARCHAR2、CLOBまたはBLOB)を使用する場合は、is jsonチェック制約を使用して、列値が有効なJSONインスタンスであることを確認することをお薦めします(例4-2を参照)。

定義上、テキストのJSONデータは、Unicodeエンコーディング(UTF-8またはUTF-16)を使用してエンコードされます。非Unicodeキャラクタ・セットで格納されたVARCHAR2またはCLOBのデータはJSONデータであるかのように使用できますが、この場合、このキャラクタ・セットはデータの処理時に自動的にUTF-8に変換されます。

JSONまたはBLOBデータ型を使用して格納されたデータは、キャラクタ・セットから独立しており、データの処理時に変換されません。

親トピック: Oracle DatabaseにおけるJSONの概要

2.2.3 JSONデータでのSQLの使用

SQLでは、JSONデータ型のコンストラクタJSON、特化された関数と条件、または単純なドット表記法を使用して、Oracle DatabaseにJSONデータを作成したり、アクセスできます。ほとんどのSQLファンクションおよび条件はSQL/JSONの標準に属していますが、一部はOracleに固有のものです。

SQL/JSON問合せファンクションjson_value、json_queryおよびjson_table。

これらは、SQL/JSONパス式をJSONデータに対して評価し、SQL値を生成します。
Oracle SQL条件json_textcontains、およびSQL/JSON条件json_exists、is json、is not json。

条件json_existsでは、特定のJSONデータが存在するかどうかがチェックされます。json_textcontainsでは、JSONデータの全文問合せを実行できます。is jsonおよびis not jsonでは、特定のJSONデータが整形式であるかどうかがチェックされます。

json_existsおよびjson_textcontainsでは、SQL/JSONパス式と一致するデータがチェックされます。
問合せファンクションjson_valueとjson_queryを組み合せたような動作の単純なドット表記法。

これは、SQLオブジェクト・アクセス式、つまり抽象データ型(ADT)の属性ドット表記法に似ています。これが、データベースのJSONデータを問い合せる最も簡単な方法です。
SQL/JSONの生成関数であるjson_object、json_array、json_objectaggおよびjson_arrayagg。

これらは、JSONオブジェクトおよび配列データを(SQL値として)生成するためにSQLデータを集めます。
Oracle SQLファンクションjson_serialize、json_scalar、およびOracle SQL条件json_equal。

ファンクションjson_serializeは、JSONデータのテキスト表現を返します。json_scalarは、指定されたSQLスカラー値に対応するJSON型のスカラー値を返します。json_equalは、2つのJSON値が同じかどうかをテストします。
JSONデータ型のコンストラクタJSON。

これは、テキストのJSONデータを解析して、SQLデータ型JSONのインスタンスを作成します。
Oracle SQL集計関数json_dataguide。

これにより、データ・ガイドであるJSONデータが生成され、これを使用してデータベース内の他のJSONデータの構造およびコンテンツに関する情報を検出できます。

問合せのシンプルな例として、表j_purchaseorder(ここでは、poの別名を使用)のJSON列po_documentに格納された文書のドット表記法の問合せを次に示します。これは、すべての発注書の要求者を取得します(JSONフィールドRequestor)。

SELECT po.po_document.Requestor FROM j_purchaseorder po;

親トピック: Oracle DatabaseにおけるJSONの概要

2.2.4 JSONデータでのPL/SQLの使用

JSONデータ型インスタンスはPL/SQLサブプログラムの入力および出力として使用でき、JSONにSQLコードまたはPL/SQLオブジェクト型を使用して、PL/SQLコード内でJSONデータを操作できます。

一般に、SQLコードは、JSONデータにアクセスするSQLコードも含めて、PL/SQLコード内で使用できます。

次のSQLファンクションおよび条件は、組込みPL/SQLファンクションとしても使用できます: json_value、json_query、json_object、json_array、json_scalar、json_serialize、json_exists、is json、is not jsonおよびjson_equal。

BOOLEANデータ型がないOracle SQLの場合と異なり、PL/SQLでは、次のようになります。

json_exists、is json、is not jsonおよびjson_equalは、ブール関数です。
json_valueは、BOOLEAN値を返すことができます。
json_scalarは、引数としてBOOLEAN値を受け取ることができ、その場合、ブール型のJSON型インスタンス(trueまたはfalse)を返します。

JSON向けのPL/SQLオブジェクト型もあり、インメモリーJSONデータの細かい構成および操作に使用できます。テキストのJSONデータに戻って、イントロスペクション、変更およびシリアライズを行えます。

JSONデータ型のインスタンスは、PL/SQLサブプログラムの入力および出力として使用できます。そのようなデータは、JSON_OBJECT_TなどのJSONオブジェクト型をインスタンス化することによって、PL/SQLで操作できます。

親トピック: Oracle DatabaseにおけるJSONの概要

2.3 JSONデータ型(他のSQLデータ型との変換)

SQLデータ型JSONとは、ネイティブ・バイナリ形式OSONを使用したJSONデータを意味します。OSONは、Oracle DatabaseサーバーとOracle Databaseクライアントの両方で問合せおよび更新を迅速に行うための、オラクル社による最適化された形式です。他のSQLデータからJSON型インスタンスを作成できます(およびその逆に作成できます)。

JSON型以外にJSONデータをサポートする他のSQLデータ型には、VARCHAR2、CLOBおよびBLOBがあります。このJSON型以外のデータは、テキストのJSONデータまたはシリアライズされたJSONデータと呼ばれます。これは解析されていない文字データです(データはUTF-8でエンコードされたバイトのシーケンスであるため、BLOBインスタンスとして格納されている場合でも)。

データ型JSONを使用すると、コストのかかるテキストJSONデータ解析が回避され、問合せのパフォーマンスが向上します。

テキストのJSONデータは、型コンストラクタJSONを使用して解析することによって、JSON型のデータに変換できます。JSON型のデータベース列に挿入するJSONテキストは、暗黙的に解析されます。コンストラクタを明示的に使用する必要はありません。

逆の方向の場合は、SQL/JSONファンクションjson_serializeを使用して、JSON型のデータをテキストのJSONデータに変換できます。JSONテキスト・データ型(VARCHAR2、CLOBまたはBLOB)のデータベース列に挿入するJSON型のデータは、暗黙的にシリアライズされます。json_serializeを明示的に使用する必要はありません。

JSON型のデータにOracle固有のスカラーJSON型(日付など)が使用されるかどうかにかかわらず、シリアライズされたJSONデータは常にJSON標準に準拠しています。

SQL/JSON生成関数(json_object、json_array、json_objectaggおよびjson_arrayagg)を使用して、JSON型以外のデータから複雑なJSON型のデータを作成できます。

Oracle SQLファンクションjson_scalarを使用すると、スカラーJSON値を持つJSON型インスタンスを作成できます。特に、Oracle固有のJSON言語型(JSON標準の一部ではない日付など)を値に使用できます。

逆の方向の場合は、SQL/JSONファンクションjson_valueを使用してJSON型のデータに問合せを行い、SQLオブジェクト型またはコレクション型のインスタンスを返すことができます。

JSONデータ型、そのコンストラクタJSONおよびOracle SQLファンクションjson_scalarは、データベース初期化パラメータcompatibleが少なくとも20の場合にのみ使用できます。それ以外の場合、それらのいずれかを使用しようとするとエラーが発生します。

注意:

JSONデータ型のインスタンスは、=、>などの演算子を使用して直接比較できません。これは、ORDER BYまたはGROUP BYとともにそれらを使用できないことを意味します。

ただし、json_valueまたは単純なドット表記法構文をデータ型変換項目メソッドとともに使用して、JSON型インスタンスからSQLスカラー値を抽出し、抽出された値に対してそのような比較演算子を使用することはできます。

JSONデータ型のコンストラクタ
JSONデータ型のコンストラクタJSONは、テキストのJSON値(スカラー、オブジェクトまたは配列)を入力として受け取り、その値を解析して、値をJSON型インスタンスとして返します。
Oracle SQLファンクションJSON_SCALAR
Oracle SQLファンクションjson_scalarは、SQLスカラー値を入力として受け取り、対応するJSONスカラー値をJSON型インスタンスとして返します。特に、Oracle固有のJSON言語型(JSON標準の一部ではない日付など)を値に使用できます。
Oracle SQLファンクションJSON_SERIALIZE
Oracle SQLファンクションjson_serializeは、入力としてJSONデータ(任意のSQLデータ型、JSON、VARCHAR2、CLOBまたはBLOB)を受け取り、そのテキスト表現を返します。
JSONコンストラクタ、JSON_SCALARおよびJSON_SERIALIZE: サマリー
コンストラクタJSON、ファンクションjson_scalarおよびファンクションjson_serializeの関係についての概要を示します。
JSON型データへのテキストのJSONデータの移行
JSONデータ型を使用してデータベースにJSONデータを格納することをお薦めします。オンライン再定義を使用して、テキストのJSON記憶域(VARCHAR2、CLOBまたはBLOB)からJSON型の記憶域に既存のデータを移行できます。

関連項目

関連項目:

JSONデータ型の詳細は、『Oracle Database SQL言語リファレンス』
コンストラクタJSONの詳細は、Oracle Database SQL言語リファレンス
Oracle SQLファンクションjson_scalarの詳細は、『Oracle Database SQL言語リファレンス』
Oracle SQLファンクションjson_serializeの詳細は、Oracle Database SQL言語リファレンスを参照してください

親トピック: Oracle DatabaseのJSON

2.3.1 JSONデータ型コンストラクタ

JSONデータ型のコンストラクタJSONは、テキストのJSON値(スカラー、オブジェクトまたは配列)を入力として受け取り、その値を解析して、値をJSON型インスタンスとして返します。

たとえば、入力がSQL文字列'{}'の場合、返されるJSON型インスタンスは空のオブジェクト{}です。入力が'{a : {"b":"beta", c:[+042, "gamma",]},}'の場合は、JSONインスタンス{"a":{"b":"beta","c":[42,"gamma"]}}が返されます。

(テキストの入力を解析せず、JSONの文字列値に変換するだけのOracle SQLファンクションjson_scalarの動作とは対照的です。json_scalar('{}')は、JSON文字列"{}"を返します。コンストラクタJSONを使用して同じJSON文字列を生成するには、明示的な二重引用符文字を追加する必要があります(JSON('"{}"'))。)

コンストラクタJSONは、データベース初期化パラメータcompatibleが少なくとも20の場合にのみ使用できます。それ以外の場合、渡される入力にかかわらず、コンストラクタでエラーが発生します。

コンストラクタJSONへの入力は、リテラルのSQL文字列、あるいはVARCHAR2、CLOBまたはBLOB型のデータのいずれかです。入力がSQLのNULL値の場合は、SQLのNULLのJSON型インスタンスが生成されます。

入力が整形式のJSONデータではない場合は、エラーが発生します。緩慢なJSON構文が使用されている場合があり、その中のオブジェクトに重複したフィールド(キー)名がある場合があります。この緩慢な構文を除き、整形式にするには、入力データがRFC 8259に準拠している必要があります。

フィールド名が重複するオブジェクトが入力にある場合は、フィールド値のいずれか1つのみが使用されます。入力に厳密な構文のみを使用するか、一意のフィールド値のオブジェクトのみにする必要がある場合は、SQL条件is jsonを使用してフィルタリングします。次のコードは、厳密ではない構文および重複したフィールドを持つオブジェクトを受け入れないようにしています。

SELECT JSON(jcol) FROM table
  WHERE jcol is json (STRICT WITH UNIQUE KEYS);

利便性のために、テキストのJSONデータを使用して、JSON型の列に対してINSERTまたはUPDATE操作を実行する場合、テキストのデータはコンストラクタJSONで暗黙的にラップされます。

コンストラクタJSONのユースケースとしては、テキストのJSONデータをJSON型に即時に解析および変換する場合があります。(かわりに、WHERE句で条件is jsonを使用することもできます。)たとえば、外部表の文字列値またはデータをバインド変数としてコンストラクタに渡すことができます。

たとえば、コンストラクタJSONを使用すると、is jsonチェック制約を使用してデータベースに格納されていないテキスト・データを整形式にすることができます。その後、結果のJSON型のデータに対して単純なドット表記法の問合せ構文を使用できます。(整形式であることが認識されていないデータには、ドット表記法を使用できません。)例2-1に、これを示します。

例2-1 JSON型へのテキストのJSONデータの即時の変換

この例では、整形式であることがデータベースに認識されていないJSONデータに対して、単純なドット表記法構文を使用しています。(コンストラクタJSONは、引数が整形式ではない場合、エラーを発生させます。)ドット表記法構文では、表の別名を使用する必要があります(この場合はj)。

WITH jtab AS
  (SELECT JSON('{ "name" : "Alexis Bull",
                 "Address": { "street" : "200 Sporting Green",
                              "city" : "South San Francisco",
                              "state" : "CA",
                              "zipCode" : 99236,
                              "country" : "United States of America" } }')
     AS jcol FROM DUAL)
  SELECT j.jcol.Address.city FROM jtab j;

関連項目

関連項目:

コンストラクタJSONの詳細は、Oracle Database SQL言語リファレンス

親トピック: JSONデータ型(他のSQLデータ型との変換)

2.3.2 Oracle SQLファンクションJSON_SCALAR

Oracle SQLファンクションjson_scalarは、SQLスカラー値を入力として受け取り、対応するJSONスカラー値をJSON型インスタンスとして返します。特に、Oracle固有のJSON言語型(JSON標準の一部ではない日付など)を値に使用できます。

ファンクションjson_scalarは、データベース初期化パラメータcompatibleが少なくとも20の場合にのみ使用できます。それ以外の場合、エラーが発生します。

json_scalarは、スカラー生成関数と考えることができます。JSONデータをサポートする任意のSQLデータ型を返すことができるSQL/JSON生成関数とは異なり、json_scalarは常にJSON型インスタンスを返します。

json_scalarの引数には、次のSQLデータ型を指定できます。VARCHAR2、RAW、CLOB、BLOB、DATE、TIMESTAMP、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND、NUMBER、BINARY_DOUBLEまたはBINARY_FLOAT。

返されるJSON型インスタンスは、OracleでサポートされるJSON言語スカラー値です。たとえば、json_scalar(current_timestamp)は、timestamp型のOracle JSON値を返します(SQLデータ型JSONのインスタンスとして)。

表2-1 JSON_SCALARによる型変換: SQL型からOracle JSON型へ

SQL型(ソース)	JSON言語型(変換先)
`VARCHAR2`	`string`
`CLOB`	`string`
`BLOB`	`binary`
`RAW`	`binary`
`NUMBER`	`number` (無限値または未定義値の場合は`string`)
`BINARY_DOUBLE`	`double` (無限値または未定義値の場合は`string`)
`BINARY_FLOAT`	`float` (無限値または未定義値の場合は`string`)
`DATE`	`date`
`TIMESTAMP`	`timestamp`
`INTERVALDAYTOSECOND`	`daysecondInterval`
`INTERVALYEARTOMONTH`	`yearmonthInterval`

例外は、正の無限大および負の無限大の数値、および数値演算の未定義の結果である値(非数値、つまりNaN)です。これらはJSON数値として表すことができません。それらの値の場合、json_scalarは数値型ではなく、JSON文字列"Inf"、"-Inf"および"Nan"をそれぞれ返します。

json_scalarによって返されるJSON型の値は、導出元のSQLデータ型を記憶しています。その後、json_value (またはjson_valueのセマンティクスを持つjson_table列)を使用してそのJSON型の値を抽出し、対応する型変換項目メソッドを使用した場合、抽出された値は元のSQLデータ型になります。たとえば、次の問合せではSQLのTIMESTAMP値が返されます。

SELECT json_value(json_scalar(current_timestamp), '$.timestamp()')
  FROM DUAL;

引数がSQLの文字列値(VARCHAR2またはCLOB)の場合、json_scalarは単純にJSON文字列値に変換します。入力はJSONデータとして解析されません。

たとえば、json_scalar('{}')は、JSON文字列値"{}"を返します。コンストラクタJSONはSQL文字列を解析するため、同じ入力に対して空のJSONオブジェクト{}を返します。コンストラクタJSONを使用して同じJSON文字列を生成するには、入力に二重引用符文字を明示的に記述する必要があります(JSON('"{}"'))。

json_scalarの引数がSQLのNULL値である場合、SQLのNULL (デフォルトの動作)またはJSONのnull (キーワードJSON NULL ON NULLを使用)を戻り値として取得できます。(SQLのNULLを返すデフォルトの動作は、JSONスカラー値が返されるルールの唯一の例外です。)

注意:

ファンクションjson_scalarはタイムスタンプ値を保持しますが、タイムスタンプからタイムゾーン情報を削除します。タイムゾーン情報は、UTC時間に変換することによって考慮されます。表2-3を参照してください。

明示的なタイムゾーン情報をJSONデータとして追加する必要がある場合は、SQLのTIMESTAMP WITH TIME ZONEインスタンスとは別にそれを記録して、JSON生成関数に渡します。例2-2に、これを示します。

例2-2 JSONデータへのタイムゾーン情報の追加

この例では、表にTIMESTAMP WITH TIME ZONE値を挿入してから、生成関数json_objectを使用してJSONオブジェクトを作成します。SQLファンクションjson_scalarおよびextractを使用して、json_objectのJSONタイムスタンプおよび数値のタイムゾーンの入力を提供します。

CREATE TABLE t (tz TIMESTAMP WITH TIME ZONE);
  INSERT INTO t
    VALUES (to_timestamp_tz('2019-05-03 20:00:00 -8:30','YYYY-MM-DD HH24:MI:SS TZH:TZM'));

-- This query returns the UTC timestamp value "2019-05-04T04:30:00"
SELECT json_scalar(tz) FROM t;

-- Create a JSON object that has 3 fields:
--  timestamp:       JSON timestamp value (UTC time): 
--  timeZoneHours:   hours component of the time zone, as a JSON number
--  timeZoneMinutes: minutes component of the time zone, as a JSON number

SELECT json_object('timestamp'       : json_scalar(tz),
                   'timezoneHours'   : extract(TIMEZONE_HOUR FROM tz),
                   'timezoneMinutes' : extract(TIMEZONE_MINUTE FROM tz))
  FROM t;

-- That query returns a JSON object and prints it in serialized form.
-- The JSON timestamp value is serialized as an ISO 8601 date-time string.
-- The time-zone values (JSON numbers) are serialized as numbers.
--
-- {"timestamp"       : "2019-05-04T04:30:00",
--  "timezoneHours"   : -8,
--  "timezoneMinutes" : -30}

関連項目

関連項目:

Oracle SQLファンクションjson_scalarの詳細は、『Oracle Database SQL言語リファレンス』

親トピック: JSONデータ型(他のSQLデータ型との変換)

2.3.3 Oracle SQLファンクションJSON_SERIALIZE

Oracle SQLファンクションjson_serializeは、入力としてJSONデータ(任意のSQLデータ型、JSON、VARCHAR2、CLOBまたはBLOB)を受け取り、そのテキスト表現を返します。

通常、問合せの結果を変換するには、json_serializeを使用します。エラー句およびRETURNING句がサポートされています。結果にフォーマット出力を指定でき、結果を切り捨てて戻り型に適合させることができます。

ファンクションjson_serializeは、常にJSON標準(RFC 8259)に準拠するJSONデータを生成します。返されるデータに使用されるのは、JSON言語の標準のデータ型(オブジェクト、配列、スカラー型の文字列、数値、ブールおよびNull)のみです。

ただし、シリアライズされる格納されているJSONデータには、OracleによってJSON言語に追加されたスカラー型(binary、date、timestamp、year-month interval、day-second interval、doubleおよびfloat)の値も使用できます。このような型のJSONデータは、シリアライズされるときに表2-2に従って変換されます。

表2-2 JSON_SERIALIZEによるOracle JSON言語型から標準JSON言語型への変換

Oracle型	標準型	ノート
binary	string	変換は、SQLファンクション`rawtohex`を使用した場合と同等です。バイナリのバイトが、それらの値を表す16進文字に変換されます。
date	string	文字列はISO 8601の日付形式です(YYYY-MM-DD)。たとえば、`"2019-05-21"`のようになります。
day-second interval	string	文字列は、SQLファンクション`to_dsinterval`に指定されたds_iso_formatに対応するISO 8601の継続時間形式です。 PdDThHmMsS。ここで、d、h、mおよびsは、それぞれ日、時間、分および秒の数字です。たとえば、`"P0DT06H23M34S"`などです。 sには、整数部の数字とそれに続く小数点および小数部の数字を指定することもできます。たとえば、`P1DT6H23M3.141593S`などです。値がゼロの数字は、その指定子とともに省略されます。たとえば、`"PT3M3.141593S"`などです。ただし、すべての数字がゼロ値である場合、構文は`"P0D"`になります。
double	number	変換は、SQLファンクション`to_number`を使用した場合と同等です。
float	number	変換は、SQLファンクション`to_number`を使用した場合と同等です。
timestamp	string	文字列はISO 8601の日時形式YYY-MM-DDThh:mm:ss.ssssssです。例: `"2019-05-21T10:04:02.340129"`
year-month interval	string	文字列は、SQLファンクション`to_yminterval`に指定されたym_iso_formatに対応するISO 8601の継続時間形式です。 PyYmM。ここで、yは年の数字、およびmは月の数字です。たとえば、`"P7Y8M"`などです。年または月がゼロの場合は、それと指定子が省略されます。たとえば、`"P7Y"`、`"P8M"`などです。ただし、年と月がゼロの場合、構文は`"P0Y"`になります。

json_serializeを使用すると、バイナリのJSONデータをテキスト形式(CLOBまたはVARCHAR2)に変換することや、フォーマット出力やASCII Unicode以外の文字のエスケープによってテキストのJSONデータを変換することができます。重要なユースケースとして、BLOBまたはJSON型の列に格納されているJSONデータをシリアライズする場合があげられます。

(JSONデータ型は、データベース初期化パラメータcompatibleが少なくとも20の場合にのみ使用できます。)

BLOBの結果はAL32UTF8キャラクタ・セットです。ただし、json_serializeによって返されるデータ型に関係なく、返されるデータはテキストのJSONデータを表します。

関連項目:

Oracle SQLファンクションjson_serializeの詳細は、Oracle Database SQL言語リファレンスを参照してください
SQLファンクションrawtohexの詳細は、Oracle Database SQL言語リファレンスを参照してください。
SQLファンクションto_numberの詳細は、Oracle Database SQL言語リファレンスを参照してください

例2-3 JSON_SERIALIZEを使用したJSON型またはBLOBデータのフォーマット出力テキストへの変換

この例では、表j_purchaseorderの列po_documentから選択されたフィールドPONumberデータの値として1600を持つJSON発注書をシリアライズしてフォーマット出力します。戻り値のデータ型は、VARCHAR2(4000) (デフォルトの戻り型)です。

例4-1では、JSON型の列を持つ表の作成方法を示しています。json_serializeを使用してBLOBデータをシリアライズすることもできます。JSONデータのBLOB列を含む表を作成する方法は、例9-1を参照してください。

SELECT json_serialize(po_document PRETTY) FROM j_purchaseorder;

関連項目

親トピック: JSONデータ型(他のSQLデータ型との変換)

2.3.4 JSONコンストラクタ、JSON_SCALARおよびJSON_SERIALIZE: サマリー

コンストラクタJSON、ファンクションjson_scalarおよびファンクションjson_serializeの関係についての概要を示します。

コンストラクタJSONとOracle SQLファンクションjson_scalarはどちらもJSON以外のSQL型インスタンスを受け取り、JSONデータ型のインスタンスを返します。

コンストラクタは、テキストのJSONデータのみを入力として受け取ります(VARCHAR2、CLOBまたはBLOBのインスタンス)。他の入力データ型の場合はエラーが発生します。

ファンクションjson_scalarは、複数のスカラーSQL型のインスタンスを入力として受け取ります。VARCHAR2またはCLOBの入力の場合は、常にJSON言語の文字列をJSON型インスタンスとして返します。

コンストラクタにより返されるJSON値は、Oracleでサポートされている任意の値(JSON object、array、string、Boolean、null、number、double、float、binary、date、timestamp、day-second intervalまたはyear-month interval値)にできます。

json_scalarによって返されるJSON値は常にスカラーです(コンストラクタの場合と同じJSON言語型(スカラー型以外(オブジェクトおよび配列)の場合を除く))。たとえば、入力がSQL型DOUBLEのインスタンスの場合は、(Oracle固有の) JSON言語型doubleの値を表すJSON型インスタンスになります。

Oracle SQLファンクションjson_serializeをJSON型インスタンスに適用した場合、結果はJSON値のテキスト(VARCHAR2、CLOBまたはBLOB)表現になり、標準ではないOracleスカラーJSON値は標準JSONスカラー値として返されます。たとえば、JSON言語型doubleの数値は、JSONのnumberのテキスト表現に変換することによってシリアライズされます。

表2-3は、JSONデータとしての様々なSQL値に対してコンストラクタJSONおよびSQLファンクションjson_scalarを使用してJSON型インスタンスを生成する場合の影響、およびそれらのインスタンスをシリアライズする場合の影響についてまとめています。コンストラクタは、入力を解析して、それがテキストのJSONデータであることを確認します。そうでない場合は、エラーが発生します。ファンクションjson_scalarは、入力のSQLスカラー値をJSON言語のスカラー値に変換します。VARCHAR2またはCLOBをjson_scalarに入力すると、常にJSON文字列値が生成されます(入力はJSONデータとして解析されません)。

次の事実を除き、コンストラクタによって生成された値をシリアライズした結果は、コンストラクタが受け取った値と同じテキスト表現です(テキストのSQLデータ型は同じであるとは限りません(VARCHAR2、CLOBおよびBLOB))。

コンストラクタは緩慢なJSON構文を受け入れますが、json_serializeは常に厳密な構文を返します。
入力のJSONオブジェクトに重複したフィールド名がある場合、1つのフィールド値のペア以外のすべてがコンストラクタによって削除されます。
通常、オブジェクト内のフィールド値のペアの順序は維持されません。出力順序が入力順序と異なる場合があります。

表2-3 コンストラクタJSONおよびOracle SQLファンクションJSON_SCALARの影響

入力SQL値	SQL型	JSONコンストラクタからのJSON値	JSON_SCALARからのJSONスカラー値
`{a:1}`	`VARCHAR2`	フィールド`a`および値`1`を持つJSONオブジェクト `json_serialize`の結果: `{"a":1}`	`{"a":1}`というテキストを含むJSON文字列 `json_serialize`の結果: `"{\"a\":1}"` (二重引用符文字でエスケープされています)
`[1,2,3]`	`VARCHAR2`	要素`1`、`2`、`3`を持つJSON配列 `json_serialize`の結果: `[1,2,3]`	`[1,2,3]`というテキストを含むJSON文字列 `json_serialize`の結果: `"[1,2,3]"`
`true`	`VARCHAR2`	JSONブール値`true` `json_serialize`の結果: `true`	`true`というテキストを含むJSON文字列 `json_serialize`の結果: `"true"`
`null`	`VARCHAR2`	JSON値`null` `json_serialize`の結果: `null`	`null`というテキストを含むJSON文字列 `json_serialize`の結果: `"null"`
SQLの`NULL`	`VARCHAR2`	SQLの`NULL` (`JSON`型) — JSON値`null`ではありません `json_serialize`の結果: SQLの`NULL`	SQLの`NULL` (`JSON`型) — JSON値`null`ではありません `json_serialize`の結果: SQLの`NULL`
`"city"`	`VARCHAR2`	`city`というテキストを含むJSON文字列 `json_serialize`の結果: `"city"`	`"city"` (二重引用符文字が含まれています)というテキストを含むJSON文字列 `json_serialize`の結果: `"\"city\""`(二重引用符文字がエスケープされています)
`city`	`VARCHAR2`	エラー — 入力は有効なJSONデータではありません(JSONスカラー値`city`がありません)	`city`というテキストを含むJSON文字列 `json_serialize`の結果: `"city"`
`3.14`	`VARCHAR2`	JSONの数値`3.14` `json_serialize`の結果: `3.14`	`3.14`というテキストを含むJSON文字列 `json_serialize`の結果: `"3.14"`
`3.14`	`NUMBER`	エラー — テキストのJSONデータではありません(`VARCHAR2`、`CLOB`および`BLOB`以外のSQL型はサポートされません)	JSONの数値`3.14` `json_serialize`の結果: `3.14`
`3.14`	`DOUBLE`	エラー — テキストのJSONデータではありません(`VARCHAR2`、`CLOB`および`BLOB`以外のSQL型はサポートされません)	JSONのdouble値`3.14` (OracleによるJSON言語の拡張) `json_serialize`の結果: `3.14`
`to_date('20.07.1974')`を評価することによって生成されるSQL日付値	`DATE`	エラー — テキストのJSONデータではありません	JSONのdate値(OracleによるJSON言語の拡張) `json_serialize`の結果: ISO 8601の文字列`"1974-07-20T00:00:00"` (UTC日付 — 入力形式は無視されます)
`to_timestamp_tz('2019-05-23 11:31:04.123 -8', 'YYYY-MM-DD HH:MI:SS.FF TZH')`を評価することによって生成されるSQLのタイムスタンプ値	`TIMESTAMP WITH TIMEZONE`	エラー — テキストのJSONデータではありません	JSONのタイムスタンプ値(OracleによるJSON言語の拡張) `json_serialize`の結果: ISO 8601の文字列`"2019-05-23T19:31:04.1 23000"` (UTC日付+時刻、タイムゾーン・オフセットから調整されます)

関連項目:

コンストラクタJSONの詳細は、Oracle Database SQL言語リファレンス
Oracle SQLファンクションjson_scalarの詳細は、『Oracle Database SQL言語リファレンス』
Oracle SQLファンクションjson_serializeの詳細は、Oracle Database SQL言語リファレンスを参照してください

親トピック: JSONデータ型(他のSQLデータ型との変換)

2.3.5 テキストのJSONデータからJSON型データへの移行

JSONデータ型を使用してデータベースにJSONデータを格納することをお薦めします。オンライン再定義を使用して、テキストのJSON記憶域(VARCHAR2、CLOBまたはBLOB)からJSON型の記憶域に既存のデータを移行できます。

オンライン再定義を実行する場合は、PL/SQLプロシージャDBMS_REDEFINITION.start_redef_tableのcol_mapping入力パラメータに、コンストラクタJSONをマッピング関数として指定するだけです。

たとえば、text_jcolがテキストのJSONデータのソース列で、json_type_colがJSONデータ型の生成後の列である場合は、col_mappingパラメータを次のように指定します。

BEGIN
  DBMS_REDEFINITION.start_redef_table(
    ...
    col_mapping => 'JSON(text_jcol) json_type_col');
END;

関連項目:

Oracle Database管理者ガイド

親トピック: JSONデータ型(他のSQLデータ型との変換)

2.4 JSONに対するOracle Databaseのサポート

JavaScript Object Notation (JSON)に対するOracle Databaseのサポートは、リレーショナル記憶域の使用範囲とJSONデータの問合せの使用範囲のベスト・フィットを実現することにより、リレーショナル問合せとJSON問合せを互いに連携して機能させることを目指しています。Oracle SQL/JSONサポートは、SQL標準のJSONサポートと密接に連携しています。

RFC 8259のサポート: JSONスカラー
リリース20c以降のOracle DatabaseではIETF RFC 8259をサポートできるため、最上位レベルのJSONスカラー値のみをJSON文書に含めることができます。このサポートは、JSONデータを返すファンクションはスカラーJSON値を返すこともできることを意味します。

関連項目:

ISO/IEC 9075-2:2016, Information technology--Database languages--SQL--Part 2: Foundation (SQL/Foundation)
ISO/IEC TR 19075–6
Oracle Database SQL言語リファレンス
JSON.org
ECMA International

親トピック: Oracle DatabaseのJSON

2.4.1 RFC 8259のサポート: JSONスカラー

リリース20c以降のOracle DatabaseではIETF RFC 8259をサポートできるため、最上位レベルのJSONスカラー値のみをJSON文書に含めることができます。このサポートは、JSONデータを返すファンクションはスカラーJSON値を返すこともできることを意味します。

これをサポートするには、データベース初期化パラメータcompatibleが20以上である必要があります。

20cより前のデータベース・リリースでは、IETF RFC 4627のみがサポートされていました。JSON文書の最上位レベルに、スカラーではなくJSONオブジェクトまたは配列のみを含めることができます。RFC 8259のサポートには、RFC 4627のサポート(およびRFC 7159のサポート)が含まれています。

compatibleパラメータが20以上の場合は、JSONデータの格納方法にかかわらず、デフォルトでRFC 8259がサポートされます。ただし、新しいis jsonのキーワードDISALLOW SCALARSを指定して、特定のJSON列に対してis jsonチェック制約を使用すると、最上位レベルのJSONスカラーがある文書の挿入を除外できます(つまり、RFC 8259ではなくRFC 4627のみをサポートします)。

compatibleパラメータが20以上の場合、SQL/JSONファンクションjson_query (または、json_queryのセマンティクスを持つjson_tableの列)にキーワードDISALLOW SCALARSを使用すると、戻り値がJSONオブジェクトまたは配列である必要があることを指定できます。これらのキーワードを指定しない場合は、JSONスカラーを返すことができます。

compatibleパラメータが20以上の場合は、SQLデータ型JSON、そのコンストラクタJSONおよびOracle SQLファンクションjson_scalarも使用できます。compatibleが20より低位にある場合、それらの使用を試みるとエラーが発生します。

compatibleが20以上の場合は、キーワードDISALLOW SCALARSを使用することにより、一部のJSONデータで最上位レベルのスカラーが許可されないように制限できます。たとえば、DISALLOW SCALARSを指定したis jsonのチェック制約を使用すると、最上位レベルのスカラーJSON値を持つ文書の挿入を防ぐことができます。

警告:

compatibleパラメータの値を20以上に変更した場合、後で低い値に戻すことはできません。

親トピック: JSONに対するOracle Databaseのサポート