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 In-Database JavaScript - Oracle Database Multilingual Engine for JavaScript (MLE)オブジェクトは、コレクションおよび文書を表します。
SODA for Node.js—Node.jsのクラスは、コレクションおよび文書を表します。
SODA for Python—Pythonのオブジェクトは、コレクションおよび文書を表します。

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

Oracle DatabaseでJSONを使用する前に
通常、Oracle DatabaseでJSONデータを使用する際には、次のことを行います: (1) SQLデータ型がJSONの列を含む表を作成し、(2)その列にJSONデータを挿入し、(3)その列内のデータを問い合せます。
Oracle DatabaseにおけるJSONの概要
Oracle Databaseは、トランザクション、索引付け、宣言的問合せおよびビューを含むリレーショナル・データベースの機能を使用してJSONをネイティブにサポートしています。リレーショナル・データとは異なり、データを定義するスキーマを必要とせずに、JSONデータをデータベース内に格納したり、索引付けおよび問合せを行うことができます。
JSONデータ型
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) SQLデータ型がJSONの列を含む表を作成し、(2)その列にJSONデータを挿入し、(3)その列内のデータを問い合せます。

主キー列、およびSQLデータ型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数値)を抽出します(例15-1も参照)。
```
SELECT po.po_document.PONumber FROM j_purchaseorder po;
```
次の問合せでは、各文書から、JSON phoneオブジェクトの配列(フィールドShippingInstructionsの値のオブジェクトのフィールドPhoneの値)を抽出しています(例15-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データを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形式(OSON)を使用して、Oracleでは、JSON標準に含まれていないdateやdoubleなどのスカラー型を追加することでJSON言語を拡張します。SQLデータ型JSONではOSON形式が使用されます。

JSONデータのデータ型
SQLデータ型JSONは、高速問合せおよび更新のためのOracleのバイナリJSON形式です。標準のJSONスカラー型(数値、文字列、ブールおよびnull)が拡張され、SQLスカラー型に対応する型が追加されます。これにより、JSONとSQLの間のスカラー・データの変換が単純で無損失になります。
JSONのnullとSQLのNULL
SQLコードとJSONコードの両方が関与する場合は、"null"が関わるとコードとその説明がわかりにくくなることがあります。JSON言語のnull値とSQLのNULL値の区別には、場合によっては、よく注意を払う必要があります。また、SQL NULL自体が混乱を招く可能性があります。
データベース表の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データとのJavaScriptの使用
Oracle Database Multilingual Engine (MLE)を使用すると、PL/SQLまたはSQLコードと、データベース・サーバーで実行されているJavaScriptコードとの間で、JSONデータを交換できます。node-oracledbドライバを使用すると、データベース・クライアントでJavaScriptコードを実行できます。

関連項目

関連項目:

JSONスキーマの詳細は、json-schema.orgを参照してください。

親トピック: 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型を使用することをお薦めします。

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

ノート:

わかりやすいように、このドキュメントでは通常は、JSON言語での型を"JSON言語型"と呼び、SQLデータ型JSONを"JSON型"と呼びます。この呼び方によく注意を払うと、特定のコンテキストでのJSONの"型"の意味を区別しやすくなります。

JSONデータを使用するSQLコードには、両方の言語(SQLとJSON)の式を含めることができます。SQLコード内では、リテラルJSONコードは、通常は一重引用符文字(')で囲みます。この'...'という言語境界に注意を払うと、理解しやすくなります。

JSONデータのSQL型がJSON以外(VARCHAR2、CLOBまたはBLOB)である場合、JSONデータはテキストと呼ばれます。これは、解析対象外の文字データです(BLOBインスタンスとして格納されている場合でも対象外)。

データベース内の既存のテキストJSONデータをJSON型のデータに移行できるため、そのようにすることをお薦めします。「テキストJSONデータからJSONデータ型への移行」を参照してください。

JSONデータがSQLデータ型JSONの場合は、標準のJSON言語のスカラー型(数値、文字列、ブールおよびnull)のセットがOracleによって拡張され、SQLスカラー型に対応する複数の型が含まれます(binary、date、timestamp、timestamp with time zone、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またはBLOBに対応しています。
date — SQLのDATEに対応しています。
timestamp — SQLのTIMESTAMPに対応しています。
timestamp with time zoneは、SQL TIMESTAMP WITH TIME ZONEに対応します。
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パス式項目メソッドtype()を使用して、JSONスカラー値のJSON言語型を決定できます。

型名は、"binary"、"date"、"timestamp"、"timestamp with time zone"、 "yearmonthInterval"、"daysecondInterval"、"double"、"float"、"number"、"null"、"string"、"boolean"のいずれかのJSON文字列として返されます。たとえば、対象となるスカラーJSON値がタイムゾーン付きのタイムスタンプ型の場合、type()は文字列"timestamp with time zone"を返します。参照:

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のnullとSQLのNULL

SQLコードとJSONコードの両方が関与する場合は、"null"が関わるとコードとその説明がわかりにくくなることがあります。JSON言語のnull値とSQLのNULL値の区別には、場合によっては、よく注意を払う必要があります。また、SQL NULL自体が混乱を招く可能性があります。

JSON言語では、nullは、(JSON言語)型の値と名前の両方です。型nullに指定できる値はnullのみです。
SQLでは、各データ型にNULL値があります。型VARCHAR2にはNULL値があり、型NUMBERにもあり、その他も同様であり、型JSON (JSONデータ用のOracleによるネイティブ・バイナリ形式)にもあります。

SQLでのNULLは、通常は、値がないことを表します(欠落、不明または適用不可のデータ)。ただし、SQLでは、値がないことと、(SQL) NULL値があることとが区別されません。

SQL値ではJSON言語のスカラー値を保持でき、JSONのnullはそのような値の1つです。この場合のSQL値は、NULLではありません(JSONデータの保持に使用されているSQL型は問わない)。

JSON型のインスタンス(たとえば、JSON型の列の行)にSQL値NULLがある場合、通常は、そのインスタンスにJSONデータがないことを意味します。

JSON値nullは、SQLに関するかぎりは、NULLでない値であり、それはSQL値NULLではありません。具体的に述べると、SQL条件IS NULLでは、JSONのnull値の場合はfalseが返され、SQL条件IS NOT NULLではtrueが返されます。また、SQL/JSON条件json_existsでは、それによる存在確認対象の値がJSONのnullである場合は、trueが返されます。

SQL/JSONファンクションjson_valueでは、その入力JSONデータからSQLスカラー値が抽出されます。抽出する値がJSONのnullである場合、json_valueでは、デフォルトで、SQLのNULLが返されます。(ON ERROR処理句またはON EMPTY処理句を使用することで、json_valueの特定の使用についてこの動作をオーバーライドできます。)

ただし、SQL/JSONファンクションjson_queryの場合や単純なドット表記法の問合せの場合は異なります。それらではJSONデータが返されます。データベースでJSONデータ型がサポートされており、抽出する値がJSONのnullである場合は、どちらでも、既存のJSON null値がそのまま返されます(つまり、それらによって、json_scalar('null')で返される内容が返される)。

json_valueの目的は、JSONデータから抽出するJSONスカラー値に対応する、SQLスカラー値を返すことであると覚えておいてください。たとえばSQLの値TRUEがJSONのtrueに対応し、SQLの数値42がJSONの数値42に対応しているというような、JSONのnullに対応するSQLスカラー値はありません。OracleのJSONデータ型にはnullスカラー値がありますが、SQLには同等のスカラー値はありません。

Q: JSON値nullのSQL型はどれですか。

A: コード/コンテキストによって異なります。JSONデータの格納に使用可能などのSQL型にもできます。「JSONデータのデータ型」を参照してください。

Q: 問合せ結果セットにJSONのnull値とSQLのNULL値が両方とも存在する場合、それらの順序はどのように決まりますか。

A: 返された行にSQLのNULL値が含まれている場合、デフォルトでは、その行は、昇順でソートすると順序内で最後になり、降順でソートすると最初になります。キーワードNULLS FIRSTまたはNULLS LASTを使用すると、このデフォルト動作をオーバーライドできます。『Oracle Database SQL言語リファレンス』のSELECTを参照してください。

JSONデータからスカラー値を抽出するときには、次のことが起こる可能性があります:

入力JSONデータ自体が(SQL) NULLであるため値が選択されない。これは、たとえば、データの行がNULLである場合です。
入力JSONデータは(SQL) NULLではないが問合せ(パス式など)でスカラー値が選択されない — ターゲット値がない。
問合せでJSONのnull値が選択される。

事例3の動作は、データベースでJSONデータ型がサポートされているかどうか、つまり初期化パラメータcompatibleの値が20以上かどうかで異なります。

表2-1でのすべてのデータはSQLデータです。大文字のNULLは、SQLのNULL値を示します。示されているJSONデータは、JSONデータを含むことができるSQL型(VARCHAR2やJSONなど)の内容です。JSON言語のnull値は小文字で記述されています。

表2-1 JSON型データの入力がSQLのNULLの場合、ない場合およびJSONのnullの場合のその扱い

事例	JSON入力データ	ドット表記法.a	JSON_VALUE('$.a')	JSON_QUERY('$.a')
事例1: 入力データが`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
事例2: 対象となるデータがない	`{}`	`NULL`	`NULL`	`NULL`
事例3、`JSON`型のサポートあり: JSONの`null`値が選択されている	`{"a":null}`	`JSON`型の入力の場合: `JSON`型の`null`値(`json_scalar('null')`で返されるものと同じ) それ以外の場合: `NULL`	`NULL`	`JSON`型の入力または`RETURNING JSON`の場合: `JSON`型の`null`値(`json_scalar('null')`で返されるものと同じ) それ以外の場合: `RETURNING`または入力の型の、テキストJSONの`null`値(`json_serialize(json_scalar('null'))`で返されるものと同じ)
事例3、`JSON`型のサポートなし: JSONの`null`値が選択されている	`{"a":null}`	`NULL`	`NULL`	`NULL`

ノート:

SQL NULL自体が少し混乱を招く可能性があります。ラージ・オブジェクト(LOB)のデータ型(BLOB、(N)CLOBおよびBFILE)を除き、長さ0の値を持つことができるOracle SQL型は、長さ0の値とNULL値を区別しません。このような型には、RAWおよび(N)VARCHAR(2)や(N)CHARなどの文字型が含まれます。これは実際には、このような型の「空の文字列」値が、その型のNULL値と変わらないことを意味します。

関連項目

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

2.2.3 データベース表の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.4 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.5 JSONデータでのPL/SQLの使用

JSONデータ型インスタンスは、PL/SQLサブプログラムで使用できます。

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。

JSON向けのPL/SQLオブジェクト型もあり、インメモリーJSONデータの細かい構成および操作に使用できます。オブジェクト型のデータを構築し、イントロスペクトし、修正し、比較し、ソートし、テキスト形式のJSONデータにシリアライズして戻すことができます。

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

リリース23cより前のOracle Databaseには、BOOLEANデータ型はありません。ただし、すべてのOracle DatabaseリリースのPL/SQLには、BOOLEANがあります。PL/SQLの場合(およびリリース23c以降のSQLの場合):

json_exists、is json、is not jsonおよびjson_equalは、ブール関数です。
json_valueは、BOOLEAN値を返すことができます。json_valueセマンティクスのjson_table列は、BOOLEAN型にすることができます。
json_scalarは、引数としてBOOLEAN値を受け取ることができ、その場合、ブール型のJSON型インスタンス(trueまたはfalse)を返します。
json_object、json_objectagg、json_arrayおよびjson_arrayaggは、PL/SQL値のTRUEおよびFALSEに対応する値のtrueおよびfalseが含まれたJSONオブジェクトおよび配列を生成できます。

同様に、json_transformにSQLのTRUEまたはFALSEを渡すと、それらが変換結果に含まれている場合はJSONのtrueおよびfalseにマップされます。
json_existsおよびjson_transformには、BOOLEANバインド変数を使用できます。

PL/SQLを使用すると、JSONスキーマをリレーショナル・データまたはオブジェクト・リレーショナル・データから作成できます。

PL/SQLは、JSONスキーマを使用するためのサブプログラムもDBMS_JSON_SCHEMAパッケージで提供しています。

PL/SQLファンクションまたはプロシージャのDBMS_JSON_SCHEMA.is_valid()を使用して、JSONスキーマに対してJSONデータを検証できます。このファンクションは、有効な場合に1を返し、無効な場合に0を返します(無効なデータはオプションでエラーを発生させることができます)。このプロシージャは、OUTパラメータの値として、有効な場合にTRUEを返し、無効な場合にFALSEを返します。
PL/SQLファンクションDBMS_JSON_SCHEMA.validate_reportは、妥当性チェック・エラー・レポートの読取りに使用できます。
PL/SQLファンクションDBMS_JSON_SCHEMA.is_schema_validは、JSONスキーマ標準に従って特定のJSONスキーマ自体が有効であるかどうかを確認するために使用できます。
PL/SQLファンクションDBMS_JSON_SCHEMA.describeを使用すると、表、ビュー、オブジェクト型またはコレクション型や、それらのいずれかに解決されるシノニムからJSONスキーマを生成できます。

関連項目:

JSONスキーマの詳細は、json-schema.orgを参照してください

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

2.2.6 JSONデータとのJavaScriptの使用

Oracle Database Multilingual Engine (MLE)を使用すると、PL/SQLまたはSQLコードと、データベース・サーバーで実行されているJavaScriptコードとの間で、JSONデータを交換できます。node-oracledbドライバを使用すると、データベース・クライアントでJavaScriptコードを実行できます。

MLEでは、(1) PL/SQLパッケージDBMS_MLEおよび(2)データベース内に存在するMLEモジュールを使用して動的にJavaScriptコードが実行されます。通常、MLEモジュールを使用すると、柔軟性が向上し、より適切な方法でJavaScriptコードをPL/SQLコードから分離できます。MLEモジュールはPL/SQLパッケージに似ていますが、その違いは、コードがPL/SQLではなくJavaScriptであることです。

次の方法で、データベース・サーバーで実行されているJavaScriptコードとデータベース記憶域との間でJSONデータを交換できます。

サーバー側のMLE JavaScriptドライバであるmle-js-oracledbを使用します。
MLEモジュールを参照するJavaScriptストアド・サブプログラムを使用します。サブプログラムの引数(IN、OUT、INOUT)および戻り値は、JSONデータ型にできます。
PL/SQLコードとJavaScriptコードの間でJSON値を交換するには、PL/SQLパッケージDBMS_MLE内のプロシージャを使用します。

JSON値(オブジェクト、配列およびスカラー)とJavaScript値の間での、サーバー側のMLE JavaScriptドライバであるmle-js-oracledbによって使用されるデータ型マッピングは、通常は、クライアント側のJavaScriptドライバであるnode-oracledbによって使用されるマッピングと一致します。ただし、スカラー値間のマッピングはいくつかの点で異なります - MLEの型変換を参照してください。

PL/SQLプロシージャDBMS_MLE.export_to_mleを使用すると、JSONデータをPL/SQLから動的MLE実行コンテキストにエクスポートし、そこでそれをJavaScriptコードとともに使用できます。その逆で、PL/SQLプロシージャDBMS_MLE.import_from_mleの使用により、MLE JavaScriptコードからPL/SQLにオブジェクトをインポートし、PL/SQLでそれらをJSONオブジェクトとして使用することもできます。

以前にPL/SQLプロシージャDBMS_MLE.export_to_mleを使用してJavaScript変数名とともにエクスポートした値を、現在の動的MLE実行コンテキストにインポートするには、組込みモジュールmle-js-bindingsにあるJavaScript関数importValue()を使用します。関数importValue()は、引数としてその変数名を取り、そのJSONデータのすべてのスカラー値を、対応するネイティブJavaScript型に変換して、JavaScript値を返します。

同様に、JavaScript関数exportValue()を使用して、現在の動的MLE実行コンテキストから値をエクスポートします。

関連項目:

『Oracle Database JavaScript開発者ガイド』の動的MLE実行の概要
『Oracle Database JavaScript開発者ガイド』のMLEのJavaScript関数
JavaScriptとPL/SQLまたはSQLとの間のデータ型変換(SQLデータ型JSONで表されるJSON言語型との間の変換を含む)については、『Oracle Database JavaScript開発者ガイド』のMLEの型変換。
mle-js-oracledbおよびmle-js-bindingsについては、GitHubでMLEモジュール
GitHubにあるNode.js node-oracledb

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

2.3 JSONデータ型

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データ型コンストラクタ
JSONデータ型コンストラクタのJSONは、テキストのJSON値(スカラー、オブジェクトまたは配列)を入力としてを受け取って解析し、その値をJSON型のインスタンスとして返します。また、ユーザー定義のPL/SQLまたはSQL集計型のインスタンスを入力にすることもできます。
Oracle SQLファンクションJSON_SCALAR
Oracle SQLファンクションjson_scalarは、SQLスカラー値を入力として受け取り、対応するJSONスカラー値をJSON型インスタンスとして返します。特に、Oracle固有のJSON言語型(dateなど)を値に使用できます。
Oracle SQLファンクションJSON_SERIALIZE
Oracle SQLファンクションjson_serializeは、入力としてJSONデータ(任意のSQLデータ型、JSON、VARCHAR2、CLOBまたはBLOB)を受け取り、そのテキスト表現(VARCHAR2、CLOBまたはBLOBデータ)を返します。VARCHAR2(4000)はデフォルトの戻り型です。
JSONコンストラクタ、JSON_SCALARおよびJSON_SERIALIZE: サマリー
JSONデータ型コンストラクタJSON、Oracle SQLファンクションjson_scalarおよびOracle SQLファンクションjson_serialize間の関係について概要を示します。
拡張されたスカラー値を表すテキストのJSONオブジェクト
ネイティブ・バイナリJSONデータ(OSON形式)は、SQL型に対応していてJSON標準には含まれてないスカラー型(dateなど)の追加によってJSON言語を拡張します。Oracle Databaseでは、このような非標準値を含め、JSONスカラー値を表すテキストのJSONオブジェクトの使用もサポートしています。
JSONデータ型の値の比較とソート
SQLデータ型JSONの値に対する正規のソート順について説明します。すべての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型のインスタンスとして返します。また、ユーザー定義のPL/SQLまたはSQL集計型のインスタンスを入力にすることもできます。

ノート:

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

たとえば、入力が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へのテキストの入力は、リテラルのSQL文字列か、VARCHAR2型、CLOB型またはBLOB型のいずれかのデータになります。入力がSQLのNULL値の場合は、SQLのNULLのJSON型インスタンスが生成されます。

コンストラクタへの非テキストの入力は、次のユーザー定義データ型のいずれかのインスタンスになります。

PL/SQL VARRAY
PL/SQLレコード
SQLオブジェクト型
binary_integerコレクションによるPL/SQL索引(IBBI)
PL/SQLネスト表
PL/SQL連想配列

引数としてのVARRAYインスタンスは、結果的にJSON配列になります。JSON配列の要素は、VARRAYコレクションの要素から(順番に)作成されます。

その他の各インスタンスは、結果的にJSONオブジェクトになります。JSONオブジェクトのメンバーは、レコード・インスタンスまたはSQLオブジェクト・インスタンスの属性、IBBIまたはネスト表インスタンスの索引、および連想配列インスタンスのキーと値のペアから作成されます。

コンストラクタによって返される値は、Oracleでサポートされている任意のJSON値です。これには、標準JSON言語型の値(オブジェクト、配列、文字列、ブール、nullおよび数値)が含まれます。また、非標準のOracleスカラーJSON値、つまり、Oracle固有のスカラー型(double、float、binary、date、timestamp、day-second intervalおよびyear-month interval)の値も含まれます。コンストラクタをキーワードEXTENDEDとともに使用すると、Oracle固有の型の値は、テキストのJSON入力のOracle拡張オブジェクト・パターンから導出できます。

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

テキスト入力で厳密なJSON構文のみが使用されるようにする必要がある場合は、SQL条件is jsonを使用してそれをフィルタ処理します。このコードにより、厳密でない構文が受け入れられなくなります:

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

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

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

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

例2-1 JSON型へのテキストの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型コンストラクタに関する項を参照してください。
非テキスト引数のJSON型の値への変換の詳細は、『Oracle Database PL/SQL言語リファレンス』のPL/SQL型とJSON型の変換に関する項を参照してください。

親トピック: JSONデータ型

2.3.2 Oracle SQLファンクションJSON_SCALAR

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

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

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

json_scalarの引数としては、次のSQLデータ型のインスタンスを指定できます: JSON、BLOB、CLOB、NCLOB、VARCHAR2、VARCHAR、NVARCHAR2、CHAR、NCHAR、RAW、BOOLEAN、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、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のインスタンスとして)。

入力がJSON型の場合、json_scalarは次のように動作します。

JSONスカラー値に対応する入力が返されます。
JSON非スカラー値に対応する入力はエラーになります。エラー・ハンドラがデフォルトでNULL ON ERRORの場合は、SQL NULL (JSONデータ型)が返されます。

ヒント:

json_scalarは、デフォルトでは非スカラー入力に対してNULLを返し、非スカラーJSON値が関与する比較はスカラーとスカラーの比較よりもコストがかかることがあるため、JSONデータの順序付けや比較の際には、json_scalarでラップした後に簡単な手動最適化を実施することで、比較対象のデータから非スカラーを効率的にプルーニングできます。(より正確には、すぐに比較されるNULLに置換します)。

たとえば、次のようなコードがあるとします。

SELECT data FROM customers c
  ORDER BY c.data.revenue;

次を使用します。

SELECT data FROM customers c
  ORDER BY json_scalar(c.data.revenue);

ノート:

JSONパス式項目メソッドtype()を使用して、JSONスカラー値のJSON言語型を決定できます。

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

SQL型(ソース)	JSON言語型(変換先)
`VARCHAR2`、`VARCHAR`、`NVARCHAR2`、`CHAR`または`NCHAR`	string
`CLOB`または`NCLOB`	string
`BLOB`	binary
`RAW`	binary
`BOOLEAN`	ブール値
`NUMBER`	number (無限値または未定義値の場合はstring)
`BINARY_DOUBLE`	double (無限値または未定義値の場合はstring)
`BINARY_FLOAT`	float (無限値または未定義値の場合はstring)
`DATE`	date
`TIMESTAMP`	timestamp
`TIMESTAMPWITHTIMEZONE`	タイム・ゾーン付きタイムスタンプ
`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、VARCHAR、NVARCHAR、CHAR、NCHARまたは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-4を参照してください。

明示的なタイムゾーン情報を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言語リファレンス』のOracle Database SQL言語リファレンスを参照してください
『Oracle Database SQL言語リファレンス』のJSONデータ型
SQLデータ型CHAR、NCHAR、VARCHAR2、VARCHARおよびNVARCHAR2については、『Oracle Database SQL言語リファレンス』の文字のデータ型
SQLデータ型BLOB、CLOBおよびNCLOBについては、『Oracle Database SQL言語リファレンス』のラージ・オブジェクト(LOB)のデータ型
SQLデータ型NUMBER、BINARY_DOUBLEおよびBINARY_FLOATについては、『Oracle Database SQL言語リファレンス』の数値のデータ型
SQLデータ型DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、INTERVAL YEAR TO MONTHおよびINTERVAL DAY TO SECONDについては、『Oracle Database SQL言語リファレンス』の日時および間隔のデータ型
『Oracle Database SQL言語リファレンス』のブール・データ型
SQLデータ型RAWについては、『Oracle Database SQL言語リファレンス』のRAWおよびLONG RAWデータ型

親トピック: JSONデータ型

2.3.3 Oracle SQLファンクションJSON_SERIALIZE

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

通常、問合せの結果を変換するには、json_serializeを使用します。ファンクションでは、エラー句およびRETURNING句がサポートされています。必要に応じて、次の任意の組合せを実行できます。

標準のASCII Unicodeエスケープ・シーケンスを使用して、すべての非ASCII Unicode文字を自動的にエスケープします(キーワードASCII)。
結果をフォーマット出力します(キーワードPRETTY)。
結果に含まれるオブジェクトのメンバーを、フィールド名でアルファベットの昇順に並べ替えます(キーワードORDERED)。

この順序は、AL32UTF8文字セットで表されるバイナリ順序のVARCHAR2照合で定義されます。つまり、文字はUnicodeのコード・ポイントに従って順序付けされます。
戻り型に適合させるように結果を切り捨てます(キーワードTRUNCATE)。
Oracle固有のスカラーJSON言語型の値をOracle拡張オブジェクト・パターンに変換します(キーワードEXTENDED)。

例2-3と例2-4を参照してください。

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

シリアライズされて格納されたJSONデータには、OracleでJSON言語に追加されたスカラー型の値が含まれることもあります。このような型のJSONデータは、シリアライズされるときに表2-3のように変換されます。たとえば、JSON言語型doubleの数値は、JSONのnumberのテキスト表現に変換することによってシリアライズされます。

ノート:

入力されたJSON string値はそのまま(変更なし)で返されます。ここで指定されている形式とは異なる形式を使用して非文字列スカラーJSON値をシリアライズする場合は、最初にto_charなどのSQL変換関数を使用して、必要に応じて書式設定された文字列値を生成し、その値をjson_serializeに渡します。

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

Oracle JSONスカラー型(type()によるレポート)	標準のJSONスカラー型	ノート
binary	string	変換は、SQLファンクション`rawtohex`を使用した場合と同等です。バイナリのバイトが、それらの値を表す16進文字に変換されます。
date	string	文字列はISO 8601の日付形式です(YYYY-MM-DD)。たとえば、`"2019-05-21"`のようになります。
daysecondInterval	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の日時形式YYYY-MM-DDThh:mm:ss.ssssssです。たとえば: `"2019-05-21T10:04:02.340129"`
タイム・ゾーン付きタイムスタンプ	string	文字列はISO 8601の日時形式YYYY-MM-DDThh:mm:ss.ssssss(+\|-)hh:mmであるか、UTCからのゼロのオフセットの場合はYYYY-MM-DDThh:mm:ss.ssssssZです。たとえば、`"2019-05-21T10:04:02.123000-08:00"`または`"2019-05-21T10:04:02.123000Z"`などです。
yearmonthInterval	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データを表します。

ノート:

JSONパス式項目メソッドtype()を使用して、JSONスカラー値のJSON言語型を決定できます。

関連項目:

Oracle SQLファンクションjson_serializeの詳細は、『Oracle Database SQL言語リファレンス』のJSON_SERIALIZEに関する項を参照してください
SQLファンクションrawtohexの詳細は、『Oracle Database SQL言語リファレンス』のRAWTOHEXに関する項を参照してください
SQLファンクションto_numberの詳細は、『Oracle Database SQL言語リファレンス』のTO_NUMBERに関する項を参照してください

例2-3 JSON_SERIALIZEを使用したJSON型またはBLOBデータのオブジェクト・メンバーが順序付けされたフォーマット出力テキストへの変換

この例では、表j_purchaseorderの列po_documentから選択されたフィールドPONumberデータの値が1600のJSON発注書をシリアル化し、オブジェクト・メンバーを順序付けしてフォーマット出力します。戻り値のデータ型は、VARCHAR2(4000) (デフォルトの戻り型)です。

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

SELECT json_serialize(po_document PRETTY ORDERED)
  FROM j_purchaseorder po
  WHERE po.po_document.PONumber = 1600;

例2-4 JSON_SERIALIZEを使用した非ASCII Unicode文字のASCIIエスケープ・コードへの変換

この例では、非ASCII文字(€)を含む文字列フィールド値があるオブジェクトをシリアライズします。また、フィールドをアルファベット順に順序付けします。

SELECT json_serialize('{"price" : 20, "currency" : "€"}' ASCII ORDERED)
  FROM DUAL;

問合せは、{"currency" : "\u20AC", "price" : 20}を返します。

関連項目

親トピック: JSONデータ型

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

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

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

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

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

コンストラクタによって返される値は、Oracle固有のスカラー型(double、float、binary、date、timestamp、day-second intervalおよびyear-month interval)の値を含め、Oracleでサポートされている任意のJSON値です。コンストラクタをキーワードEXTENDEDとともに使用すると、値は、テキストのJSON入力のOracle拡張オブジェクト・パターンから導出できます。

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

Oracle SQLファンクションjson_serializeをJSON型インスタンスに適用すると、非標準OracleスカラーJSON値が標準JSONスカラー値として返されます。ただし、json_serializeをキーワードEXTENDEDとともに使用すると、Oracle固有のスカラーJSON言語型の値は、テキストのJSON出力のOracle拡張オブジェクト・パターンにシリアライズできます。

表2-4では、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つのフィールド値のペア以外のすべてがコンストラクタによって削除されます。
通常、オブジェクト内のフィールド値のペアの順序は維持されません。出力順序が入力順序と異なる場合があります。
コンストラクタが適用されるテキストのデータに拡張JSON構造体(非標準スカラーJSON値を指定するJSONオブジェクト)が含まれる場合、結果のJSON型データには(キーワードEXTENDEDの使用により)これらの構造体をSQLスカラー値に変換した結果のスカラー値がいくつか含まれることがあります。json_serialize (キーワードEXTENDEDを指定)を結果のJSON型データに適用すると、その結果には、逆方向に変換した結果の拡張JSON構造体がいくつか含まれることがあります。

ただし、この2方向の変換は、一般的に逆方向の操作ではありません。これらはOracle構造体では真逆になりますが、Oracle以外の拡張JSON構造体では真逆になりません。拡張JSON構造体はJSON型のOracle固有のJSONスカラー値に変換されるため、拡張JSONオブジェクトとしてテキストのJSONデータにシリアライズされ直すと、元々Oracle以外の形式である場合、失われる可能性があります。

表2-4 コンストラクタ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` (大/小文字は区別されません)	`BOOLEAN`	JSONブール値`true` `json_serialize`の結果: `true`	`JSON`コンストラクタと同じです。
`true`	`VARCHAR2`	JSONブール値`true` `json_serialize`の結果: `true`	`true`というテキストを含むJSON文字列 `json_serialize`の結果: `"true"`
`null`	`VARCHAR2`	JSON値`null` `json_serialize`の結果: `null`	`null`というテキストを含むJSON文字列 `json_serialize`の結果: `"null"`
`NULL`^脚注1	`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"`
`{"$numberDouble" : "1E300"}`または`{"$numberDouble" : 1E300}` (拡張JSONオブジェクト。)	`VARCHAR2`	double型のJSONスカラー	入力の`VARCHAR2`値と同じ内容を持つJSON文字列
`{"$numberDecimal" : "1E300"}`または`{"$numberDecimal" : 1E300}` (拡張JSONオブジェクト。)	`VARCHAR2`	number型のJSONスカラー。`$numberDecimal`拡張オブジェクトから導出されたものとして内部的にタグ付けされます	入力の`VARCHAR2`値と同じ内容を持つJSON文字列
`{"$oid" : "deadbeefcafe0123456789ab"}`または`{"$rawid" : "deadbeefcafe0123456789ab"}` (拡張JSONオブジェクト。)	`VARCHAR2`	binary型のJSONスカラー。`$rawid`または`$oid`拡張オブジェクトから導出されたものとして内部的にタグ付けされます	入力の`VARCHAR2`値と同じ内容を持つJSON文字列
`{"$date" : "2020-11-24T12:34:56"}`または`{"$oracleDate" : "2020-11-24T12:34:56"}` (拡張JSONオブジェクト。)	`VARCHAR2`	date型のJSONスカラー。`$oracleDate`または`$date`拡張オブジェクトから導出されたものとして内部的にタグ付けされます	入力の`VARCHAR2`値と同じ内容を持つJSON文字列
`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`	`BINARY_DOUBLE`	エラー — テキストのJSONデータではありません(`VARCHAR2`、`CLOB`および`BLOB`以外のSQL型はサポートされません)	JSONのdouble値`3.14` (OracleによるJSON言語の拡張) `json_serialize`の結果: `3.14`
`3.14`	`NUMBER`。`$numberDecimal`拡張オブジェクトから導出されたものとして内部的にタグ付けされます	number型のJSONスカラー。`$numberDecimal`拡張オブジェクトから導出されたものとして内部的にタグ付けされます	元の拡張オブジェクトと同じ内容を持つJSON文字列
`RAW`値	`RAW`。`$rawid`または`$oid`拡張オブジェクトから導出されたものとして内部的にタグ付けされます	binary型のJSONスカラー。`$rawid`または`$oid`拡張オブジェクトから導出されたものとして内部的にタグ付けされます	元の拡張オブジェクトと同じ内容を持つJSON文字列
`to_date('20.07.1974')`を評価することによって生成されるSQL日付値	`DATE`	エラー — テキストのJSONデータではありません	JSONのdate値(OracleによるJSON言語の拡張) `json_serialize`の結果: ISO 8601の文字列`"1974-07-20T00:00:00"` (UTC日付 — 入力形式は無視されます)
`to_timestamp('2019-05-23 11:31:04.123', 'YYYY-MM-DD HH24:MI:SS.FF')`を評価することによって生成されるSQLのタイムスタンプ値	`TIMESTAMP`	エラー — テキストのJSONデータではありません	JSONのタイムスタンプ値(OracleによるJSON言語の拡張) `json_serialize`の結果: ISO 8601の文字列`"2019-05-23T11:31:04.123000"`
`to_timestamp_tz('2019-05-23 11:31:04.123 -8', 'YYYY-MM-DD HH24:MI:SS.FF TZH')`を評価することによって生成されるSQLのタイムスタンプ値	`TIMESTAMP WITH TIMEZONE`	エラー — テキストのJSONデータではありません	JSONのタイムゾーン付きのタイムスタンプ値(OracleによるJSON言語の拡張) `json_serialize`の結果: ISO 8601の文字列`"2019-05-23T11:31.03.123000-08:00"`

^脚注1これはVARCHAR2型のSQLのNULL値であり、文字NULLを含むSQL文字列ではありません。

関連項目

関連項目:

『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンス
『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンス
『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンス

親トピック: JSONデータ型

2.3.5 拡張されたスカラー値を表すテキストのJSONオブジェクト

ネイティブ・バイナリJSONデータ(OSON形式)は、SQL型に対応していてJSON標準には含まれてないスカラー型(dateなど)を追加することでJSON言語を拡張します。Oracle Databaseでは、このような非標準値を含め、JSONスカラー値を表すテキストのJSONオブジェクトの使用もサポートしています。

このような拡張オブジェクトが含まれるテキストのJSONデータからネイティブ・バイナリJSONデータを作成する場合、必要に応じて、対応する(ネイティブ・バイナリ)JSONスカラー値に置き換えることができます。

拡張オブジェクトの例は、{"$numberDecimal":31}です。これは、非標準型10進数のJSONスカラー値を表し、そのように解釈されると、ネイティブ・バイナリ形式の10進数に置き換えられます。

たとえば、JSONデータ型コンストラクタJSONを使用する場合、キーワードEXTENDEDを使用すると、テキストの入力で認識された拡張オブジェクトがネイティブ・バイナリJSONの結果では対応するスカラー値に置き換えられます。キーワードEXTENDEDを含めなければ、このような置換は発生しません。テキストの拡張JSONオブジェクトは、ネイティブ・バイナリ形式のJSONオブジェクトにそのまま変換されるだけです。

逆方向では、Oracle SQLファンクションjson_serializeを使用してバイナリJSONデータをテキストのJSONデータ(VARCHAR2、CLOBまたはBLOB)としてシリアライズする場合、キーワードEXTENDEDを使用して、(ネイティブ・バイナリ)JSONスカラー値を対応するテキストの拡張JSONオブジェクトに置き換えることができます。

ノート:

使用するデータベースがOracle Autonomous Databaseの場合、PL/SQLプロシージャDBMS_CLOUD.copy_collectionを使用して、Oracle NoSQL Databaseなどの一般的なNoSQLデータベースによって生成されるようなJSONデータのファイルからJSONドキュメント・コレクションを作成できます。

ejsonをプロシージャのtypeパラメータの値として使用すると、入力ファイルで認識された拡張JSONオブジェクトが、結果のネイティブ・バイナリJSONコレクションでは対応するスカラー値に置き換えられます。もう1つの方向では、ファンクションjson_serializeをキーワードEXTENDEDとともに使用して、結果のテキストのJSONデータで、スカラー値を拡張JSONオブジェクトに置き換えることができます。

拡張オブジェクトの主なユースケースを2つ、次に示します。

交換(インポート/エクスポート):
- 拡張オブジェクトが含まれる既存のJSONデータを(どこかから)取り込みます。
- データベースの外部でなんらかに使用するため、ネイティブ・バイナリJSONデータを拡張オブジェクトが含まれるテキストのJSONデータとしてシリアライズします。
ネイティブ・バイナリJSONデータの検査: 対応する拡張オブジェクトを調べて、保持しているものを確認します。

交換目的の場合、拡張オブジェクトをネイティブ・バイナリJSONスカラーに変換して、Oracle NoSQL Databaseなどの一般的なNoSQLデータベースによって生成されたファイルからJSONデータを取り込むことができます。もう1つの方向では、Oracle固有のスカラーJSON値を対応するテキストの拡張JSONオブジェクトに置き換えて、ネイティブ・バイナリJSONデータをテキストのデータとしてエクスポートすることができます。

検査の例として、{"dob" : "2000-01-02T00:00:00"}などのオブジェクトを、ネイティブJSONデータをシリアライズした結果として考えてみます。"2000-01-02T00:00:00"は、日付型のネイティブ・バイナリ値をシリアライズした結果ですか。それとも、ネイティブ・バイナリ値は単なる文字列ですか。json_serializeをキーワードEXTENDEDとともに使用すると、わかります。

拡張オブジェクト・フィールドからスカラーJSON型へのマッピングは、通常、多対1です。複数種類の拡張JSONオブジェクトを特定のスカラー値にマップできます。たとえば、拡張JSONオブジェクトの{"$numberDecimal":"31"}と{"$numberLong:"31"}は、どちらも値31のJSON言語スカラー型numberとして変換され、これらの各JSONスカラーに対して項目メソッドtype()は"number"を返します。

項目メソッドtype()は、対象値のJSON言語スカラー型を(JSON文字列として)報告します。スカラー値の中には、同じスカラー型であっても内部的に区別できるものがあります。これにより、通常、ファンクションjson_serialize (キーワードEXTENDEDを指定)による元の拡張JSONオブジェクトの再構築が可能になります。そのようなスカラー値は、その値を実装する各種のSQL型を使用するか、導出元の拡張JSONオブジェクトの種類でタグ付けすることで、内部的に区別されます。

json_serializeで元の拡張JSONオブジェクトを再構築すると、結果は元のものと必ずしもテキスト的に同一であるとはかぎりませんが、常に意味的に等しいです。たとえば、{"$numberDecimal":"31"}と{"$numberDecimal":31}は、フィールド値の型が異なっていても(文字と数値)、意味的に等しくなります。これらは同じ内部値に変換され、それぞれが$numberDecimal拡張オブジェクトから導出されたものとしてタグ付けされます(同一タグ)。ただし、シリアライズすると、どちらの結果も{"$numberDecimal":31}になります。Oracleでは常に、最も直接関連する型がフィールド値に使用されます。この場合は、スカラー型numberのJSON言語値31です。

表2-5に、使用される各種の型の間の対応関係を示します。(1)入力として使用される拡張オブジェクトの型、(2)項目メソッドtype()によって報告される型、(3)内部で使用されるSQL型、(4)ファンクションjson_serializeによる出力として使用される標準のJSON言語型、および(5)キーワードEXTENDEDが指定されている場合のjson_serializeによる拡張オブジェクト出力の型の間でマップします。

表2-5 拡張JSONオブジェクト型の関係

拡張オブジェクト型(入力)	Oracle JSONスカラー型(type()によるレポート)	SQLスカラー型	標準のJSONスカラー型(出力)	拡張オブジェクト型(出力)
JSON数値の値、数値を表す文字列、または`"Infinity"`、`"-Infinity"`、`"Inf"`、`"-Inf"`、`"Nan"`^脚注2のいずれかの文字列による`$numberDouble`	double	`BINARY_DOUBLE`	number	JSON数値の値、または`"Inf"`、`"-Inf"`、`"Nan"`^脚注3のいずれかの文字列による`$numberDouble`
`$numberDouble`の場合と同じ値の`$numberFloat`	float	`BINARY_FLOAT`	number	`$numberDouble`の場合と同じ値の`$numberFloat`
`$numberDouble`の場合と同じ値の`$numberDecimal`	number	`NUMBER`	number	`$numberDouble`の場合と同じ値の`$numberDecimal`
値が符号付き32ビット整数または数値を表す文字列の`$numberInt`	number	`NUMBER`	number	`$numberDouble`の場合と同じ値の`$numberInt`
値がJSON数値または数値を表す文字列の`$numberLong`	number	`NUMBER`	number	`$numberDouble`の場合と同じ値の`$numberLong`
値が次のいずれかの`$binary`: Base64文字の文字列フィールド`base64`および`subType`が含まれるオブジェクトで、値はそれぞれbase64文字の文字列および数値`0` (任意のバイナリ)または`4` (UUID) 値がbase64文字の文字列の場合、拡張オブジェクトには、値が0または4のフィールド`$subtype` (1バイトの整数(0から255)か、このような整数を表す2文字の16進文字列で表現される)を含めることもできます。	binary	`BLOB`または`RAW`	string 変換は、SQLファンクション`rawtohex`を使用した場合と同等です。	次のいずれか1つを使用します。値がbase64文字の文字列の`$binary` `subType`値`4` (UUID)が入力に含まれる場合、値が32文字の16進文字列の`$rawid`
値が24文字の16進文字列の`$oid`	binary	`RAW(12)`	string 変換は、SQLファンクション`rawtohex`を使用した場合と同等です。	値が24文字の16進文字列の`$rawid`
値が偶数文字の16進文字列の`$rawhex`	binary	`RAW`	string 変換は、SQLファンクション`rawtohex`を使用した場合と同等です。	値がbase64文字の文字列で、右側に`=`文字が埋め込まれている`$binary`
値が24または32文字の16進文字列の`$rawid`	binary	`RAW`	string 変換は、SQLファンクション`rawtohex`を使用した場合と同等です。	`$rawid`
値がISO 8601の日付文字列の`$oracleDate`	date	`DATE`	string	値がISO 8601の日付文字列の`$oracleDate`
値がISO 8601のタイムスタンプ文字列の`$oracleTimestamp`	timestamp	`TIMESTAMP`	string	値がISO 8601のタイムスタンプ文字列の`$oracleTimestamp`
数値のタイムゾーン・オフセットまたは`Z`が指定されたISO 8601のタイムスタンプ文字列が値の`$oracleTimestampTZ`	タイム・ゾーン付きタイムスタンプ	`TIMESTAMPWITHTIMEZONE`	string	数値のタイムゾーン・オフセットまたは`Z`が指定されたISO 8601のタイムスタンプ文字列が値の`$oracleTimestampTZ`
値が次のいずれかの`$date`: 1990年1月1日からの整数のミリ秒数 ISO 8601のタイムスタンプ文字列 1990年1月1日からの整数のミリ秒数が値のフィールド`numberLong`が含まれるオブジェクト	タイム・ゾーン付きタイムスタンプ	`TIMESTAMPWITHTIMEZONE`	string	数値のタイムゾーン・オフセットまたは`Z`が指定されたISO 8601のタイムスタンプ文字列が値の`$oracleTimestampTZ`
SQLファンクション`to_dsinterval`に指定されたISO 8601の時間隔文字列が値の`$intervalDaySecond`	daysecondInterval	`INTERVALDAYTOSECOND`	string	SQLファンクション`to_dsinterval`に指定されたISO 8601の時間隔文字列が値の`$intervalDaySecond`
値がSQLファンクション`to_yminterval`に指定されたISO 8601 interval文字列の`$intervalYearMonth`	yearmonthInterval	`INTERVALYEARTOMONTH`	string	値がSQLファンクション`to_yminterval`に指定されたISO 8601 interval文字列の`$intervalYearMonth`

^脚注2文字列値は大小文字が区別されずに解釈されます。たとえば、"NAN"、"nan"および"nAn"は受け入れられ同等であり、"INF"、"inFinity"および"iNf"も同様です。無限に大きい("Infinity"または"Inf")および小さい("-Infinity"または"-Inf")数値は、フルワードまたは略称のいずれかで受け入れられます。

^脚注3出力では、これらの文字列値のみが使用されます。完全な単語のInfinityや大小文字の変形は使用されません。

関連項目

親トピック: JSONデータ型

2.3.6 JSONデータ型の値の比較とソート

SQLデータ型JSONの値に対する正規のソート順について説明します。すべてのJSON値を比較するために使用されます。

スカラー、オブジェクト、配列のいずれであっても、任意の種類のJSONデータ型の値を直接比較またはソートできます。つまり、JSON型はWHERE句、ORDER BY句またはGROUP BY句で直接使用できるということです。正規のソート順序は、すべてのJSON型の値(テキストJSON入力でOracle拡張オブジェクト・パターンから導出されたスカラー値を含む)にわたって定義されます。

ノート:

JSONデータ型の値をSQLで比較する場合、比較する値のサイズ(SQL比較用にエンコードされた値)は32Kバイト未満である必要があります。そうでない場合は、エラーが発生します。実際には、この比較用にエンコードされたSQLサイズは、同じJSONデータのテキスト表現のサイズとほぼ同じです。

たとえば、この問合せでは、deptおよびnameフィールドのエンコードされたサイズは、それぞれ32K未満である必要があります。

SELECT * 
  FROM emp t
  WHERE t.data.dept = 'SALES' ORDER BY t.data.name

この制限は、SQL句ORDER BYおよびGROUP BY、およびSQL値比較演算子(WHERE句の>など)の使用に適用されます。

より正確には、制限はSQL自体で行われる比較およびソートにのみ適用されます。JSON言語内で実行される比較またはソートには適用されません。つまり、json_transformやjson_existsなど、JSONのSQL演算子によって実行される比較またはソートにサイズ制限はありません。特に、この制限はSQL/JSONパス式での比較には適用されません。

JSON型のソート(比較)の順序は次のとおりです。

スカラー値は、非スカラー値の前に(キーワードDESCでは後に)ソートされます。
オブジェクトは、配列の前に(キーワードDESCでは後に)ソートされます。
2つの配列は、要素を順番に比較してソートされます。対応する2つの要素が等しくない場合は、それらの要素のソート順によって2つの配列の順序が決まります。たとえば、昇順ソートでは、[4, 2, 5]は[4, 2, 9]より前にソートされます。これは、5が9より前にソートされるためです。

1つの配列のすべての要素が、それより長い配列の対応する要素と等しい場合は、短い配列が長い配列の前にソートされます。たとえば、昇順ソートでは、[4, 2]は[4, 2, 5]の前にソートされますが、[4, 1, 5]の後にソートされます。
2つのオブジェクトは、次に示すように、フィールド名、フィールド値の順にソートされます。
1. 各オブジェクトのメンバーは、フィールド名の順に並べられます。
  
  フィールド名はJSON stringの値として比較され、AL32UTF8文字セットで表されるVARCHAR2照合とバイナリ順序が使用されます。
2. ソートされたオブジェクトのメンバー(ステップ1から)は、次の順序で比較されます。
  - 2つの対応するフィールド名が異なる場合、最初にソートされるフィールド名を持つオブジェクトは、もう一方のオブジェクトの前に(キーワードDESCでは後に)ソートされます。
  - 2つの対応するフィールド名が同じ場合は、JSON型のソート順に従ってフィールドの値が比較されます。(つまり、フィールドの値は再帰的に比較されることになります)。2つの比較するオブジェクトの順序は、そのフィールド値の順序に従います。
2つの異なる型ファミリのスカラーは、この昇順でファミリごとにソートされるため、文字列の前に数値がソートされます。(降順ソートの場合は、順序が逆になります)。
1. null
2. 数値(number、double、float)
3. string
4. 非識別子binary (例: 画像)
5. 識別子binary (例: フィールド$oidまたは$rawidを含む拡張オブジェクトからの値)
6. boolean
7. 日付と時点(date、timestampまたはtimestamp with time zone)
8. yearmonthInterval
9. daysecondInterval
日時間隔値とバイナリ値は別々に比較およびソートする必要があるため、それら両方には2つの別々のファミリがあります。月ごとに異なる日数を設定できます。識別子として使用されるバイナリ値は、通常は、等価性をテストされます。等価性テストは通常は、実行可能な場合でも、非識別子のバイナリ値には役立ちません。
2つの同じ型ファミリのスカラーは、そのファミリに定義されたソート順でソートされます。たとえば、昇順ソートでは、100は使用する数値型とは無関係に200.0の前にソートされ、"cat"は使用する文字セットとは無関係に"dog"の前にソートされます。

使用されるスカラーJSON比較は対応するSQLスカラー型の照合ですが、JSONのstring比較では、AL32UTF8文字セットで表されるバイナリ順でVARCHAR2照合が使用されます。boolean値の場合、falseはtrueの前に(キーワードDESCでは後に)ソートされます。

ORDER BYで実行するようなソートの目的でJSON型の任意の値を比較できます。ただし、比較フィルタ条件など、他の目的での比較はより限定されます。

ソートは別として、同じ型ファミリ内の任意の値を比較できます。異なる型ファミリの値は常に等しくありません。等価性比較(==)はfalseを生成し、非等価性比較(!=、<>)はtrueを生成します。<、<=、>=および>の比較は、同じファミリ内でのみ意味があり、有用です。異なるファミリの値とともに使用すると、比較条件はfalseを返します。たとえば、JSONオブジェクト、数値またはブール値は、JSON配列、文字列または日付より大きくも小さくもありません。

ヒント:

たとえば、次のようなコードがあるとします。

SELECT data FROM customers c
  ORDER BY c.data.revenue;

次を使用します。

SELECT data FROM customers c
  ORDER BY json_scalar(c.data.revenue);

項目メソッドtype()を使用するとJSON値の型ファミリを特定でき、これは比較や索引付けの目的に役立ちます。ただし、これによって提供されるのは、それについての大まかなガイドのみです(通常は、JSON値の導出元となった、またはJSON値をマップできるSQLデータ型に関してのみレポートされるため)。

null型ファミリ: type()では"null"が返されます。
数値型ファミリ: type()では、数値の種類に応じて異なる型名が返されます。
- "double": SQLのBINARY_DOUBLE値に対応するJSON値(数値)の場合。これに含まれるのは、たとえば、$numberDoubleの拡張オブジェクトから導出された値です。
- "float": SQLのBINARY_FLOAT値に対応するJSON値(数値)の場合。これに含まれるのは、たとえば、$numberFloatの拡張オブジェクトから導出された値です。
- "number": (1)テキストJSON数値か文字列数値(標準のJSON言語数値型に対応)、または(2)$numberInt、$numberDecimalまたは$numberLongの拡張オブジェクトから導出されたJSON値(数値)の場合。
string型ファミリ: type()では"string"が返されます。
バイナリ型ファミリ: type()では、識別子および非識別子バイナリ・ファミリのどちらの場合も(つまり、SQLのRAW値に対応する値)、"binary"が返されます。type()では、これら2つのファミリの値を区別できません。
boolean型ファミリ: type()では"boolean"が返されます。
日付と時点ファミリの場合は、種類に応じて異なる型名が返されます。
- "date": SQLのDATE値に対応する値の場合。これに含まれるのは、たとえば、$oracleDateの拡張オブジェクトから導出された値です。
- "timestamp": SQLのTIMESTAMP値に対応する値の場合。これに含まれるのは、たとえば、$oracleTimestampの拡張オブジェクトから導出された値です。
- "timestamp with time zone": SQLのTIMESTAMP WITH TIME ZONE値に対応する値の場合。これに含まれるのは、たとえば、$dateまたは$oracleTimestampTZの拡張オブジェクトから導出された値です。($dateフィールドでは、小数秒を使用でき、協定世界時(UTC)の値が指定されるため、タイムゾーン付きタイムスタンプ値が格納されます。)
yearMonthInterval型ファミリ: SQLのINTERVAL YEAR TO MONTH値に対応する値の場合、type()では"yearmonthInterval"が返されます。これに含まれるのは、たとえば、$intervalYearMonthの拡張オブジェクトから導出された値です。
daysecondInterval型ファミリ: SQLのINTERVAL DAY TO SECOND値に対応する値の場合、type()では"daysecondInterval"が返されます。これに含まれるのは、たとえば、$intervalDaySecondの拡張オブジェクトから導出された値です。

関連項目

関連項目:

Oracle Databaseで各データ型の値が比較される方法の詳細は、『Oracle Database SQL言語リファレンス』のデータ型比較のルールに関する項を参照してください

親トピック: JSONデータ型

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

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

Oracle Databaseは、ECMAScript第5.1版で指定されたJSON形式をサポートしています。

RFC 8259のサポート: JSONスカラー
リリース21c以降の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およびJSON5
ECMAインターナショナル
ECMAScript言語仕様(JavaScript)の場合は、ECMA 262およびECMA 262第5.1版

親トピック: Oracle DatabaseのJSON

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

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

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

21cより前のデータベース・リリースでは、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のサポート