JSONデータ型

2.2 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テキストは、暗黙的に解析されます。コンストラクタを明示的に使用する必要はありません。

ノート:

解析されたJSONデータは必ず、標準IETF RFC 8259、ECMA 404およびECMA 262で定義されているとおりの厳密なJSON構文になっています。具体的に述べると、JSON型のデータが、必ず、厳密な構文になっているということです。このことは、解析されたデータが格納(永続化)されているかどうかに関係なく当てはまりまります。Oracleでは、出力時に必ずこの構文が尊重されます。(入力については、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型のデータを作成できます。

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

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

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

_________________________________________________________

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

関連トピック

関連項目:

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

親トピック: Oracle DatabaseのJSON

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

JSONデータ型コンストラクタのJSONは、テキストのJSON値(スカラー、オブジェクトまたは配列)を入力としてを受け取って解析し、その値をJSON型のインスタンスとして返します。また、SQL型VECTOR、ユーザー定義のPL/SQL型またはSQL集計型のインスタンスを入力にすることもできます。

ノート:

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

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

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

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

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

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

引数としてのVECTORインスタンスは、結果的にvector型のOracle JSONスカラー値になります。

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

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

コンストラクタによって返される値は、Oracleでサポートされている任意のJSON値です。これには、標準JSON言語型の値(オブジェクト、配列、文字列、ブール、nullおよび数値)が含まれます。また、非標準のOracleスカラーJSON値、つまり、Oracle固有のスカラー型(binary、date、day-second interval、double、float、timestamp、timestamp with time zone、vectorおよび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.2.2 SQL/JSONファンクションJSON_SCALAR

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

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

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

json_scalarの引数には、BINARY_DOUBLE、BINARY_FLOAT、BLOB、BOOLEAN、CHAR、CLOB、DATE、INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH、JSON、NCHAR、NCLOB、NUMBER、NVARCHAR2、RAW、TIMESTAMP、TIMESTAMP WITH TIME ZONE、VARCHAR、VARCHAR2またはVECTORのいずれかのSQLデータ型のインスタンスを使用できます。

返される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言語型を決定できます。

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

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

SQL型(ソース)	JSON言語型(変換先)
`VARCHAR2`、`VARCHAR`、`NVARCHAR2`、`CHAR`または`NCHAR`	string
`CLOB`または`NCLOB`	string
`BLOB`	binary
`RAW`	binary
`BOOLEAN`	boolean
`NUMBER`	number (無限値または未定義値の場合はstring)
`BINARY_DOUBLE`	double (無限値または未定義値の場合はstring)
`BINARY_FLOAT`	float (無限値または未定義値の場合はstring)
`DATE`	date
`TIMESTAMP`	timestamp
`TIMESTAMP WITH TIME ZONE`	タイム・ゾーン付きタイムスタンプ
`INTERVAL DAY TO SECOND`	daysecondInterval
`INTERVAL YEAR TO MONTH`	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を使用、キーワードJSONはオプション)
空のJSON文字列"" (キーワードEMPTY STRING 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}

関連トピック

関連項目:

SQL/JSONファンクションjson_scalarの詳細は、『Oracle Database SQL言語リファレンス』の「JSON_SCALAR」を参照してください
『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.2.3 SQL/JSONファンクションJSON_SERIALIZE

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

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

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

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

例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"`などです。
ベクター^脚注2	array	JSON配列の要素はJSON数値です。これらはvectorのSQL数値から変換されます。
yearmonthInterval	string	文字列は、SQLファンクション`to_yminterval`に指定されたym_iso_formatに対応するISO 8601の継続時間形式です。 PyYmM。ここで、yは年の数字、およびmは月の数字です。たとえば、`"P7Y8M"`などです。年または月がゼロの場合は、それと指定子が省略されます。たとえば、`"P7Y"`、`"P8M"`などです。ただし、年と月がゼロの場合、構文は`"P0Y"`になります。

^脚注2JSONベクター値はスカラー値ですが、ベクター・コンポーネントを表示するためにJSON配列としてシリアライズされます。

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

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

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

ノート:

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

ノート:

SQLファンクションvector_serializeを使用して、SQL VECTORインスタンスを数値のテキストJSON配列にシリアライズできます。(ファンクションjson_serializeは、JSONデータのみをシリアライズします。『Oracle Database SQL言語リファレンス』のvector_serializeを参照してください。)

関連項目:

SQL/JSONファンクション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の列dataから選択された、フィールドPONumberのデータの値が1600であるJSON発注書を、シリアライズし、オブジェクト・メンバーを順序付けし、整形して出力します。戻り値のデータ型は、VARCHAR2(4000) (デフォルトの戻り型)です。

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

SELECT json_serialize(data PRETTY ORDERED)
  FROM j_purchaseorder po
  WHERE po.data.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.2.4 JSONコンストラクタ、JSON_SCALARおよびJSON_SERIALIZE: サマリー

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

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

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

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

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

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

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

表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およびSQL/JSONファンクション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`^脚注3	`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 TIME ZONE`	エラー — テキストのJSONデータではありません	JSONのタイムゾーン付きのタイムスタンプ値(OracleによるJSON言語の拡張) `json_serialize`の結果: ISO 8601の文字列`"2019-05-23T11:31.03.123000-08:00"`
`VECTOR`インスタンス	`VECTOR`	vector型のJSONスカラー	vector型のJSONスカラー

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

関連トピック

関連項目:

『Oracle Database SQL言語リファレンス』JSON型コンストラクタに関する項
『Oracle Database SQL言語リファレンス』の「JSON_SCALAR」
『Oracle Database SQL言語リファレンス』の「JSON_SERIALIZE」

親トピック: JSONデータ型

2.2.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オブジェクトにそのまま変換されるだけです。

逆方向では、SQL/JSONファンクション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"`^脚注4のいずれかの文字列による`$numberDouble`	double	`BINARY_DOUBLE`	number	JSON数値の値、または`"Inf"`、`"-Inf"`、`"Nan"`^脚注5のいずれかの文字列による`$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`	タイム・ゾーン付きタイムスタンプ	`TIMESTAMP WITH TIME ZONE`	string	数値のタイムゾーン・オフセットまたは`Z`が指定されたISO 8601のタイムスタンプ文字列が値の`$oracleTimestampTZ`
値が次のいずれかの`$date`: 1990年1月1日からの整数のミリ秒数 ISO 8601のタイムスタンプ文字列 1990年1月1日からの整数のミリ秒数が値のフィールド`numberLong`が含まれるオブジェクト	タイム・ゾーン付きタイムスタンプ	`TIMESTAMP WITH TIME ZONE`	string	数値のタイムゾーン・オフセットまたは`Z`が指定されたISO 8601のタイムスタンプ文字列が値の`$oracleTimestampTZ`
SQLファンクション`to_dsinterval`に指定されたISO 8601の時間隔文字列が値の`$intervalDaySecond`	daysecondInterval	`INTERVAL DAY TO SECOND`	string	SQLファンクション`to_dsinterval`に指定されたISO 8601の時間隔文字列が値の`$intervalDaySecond`
値がSQLファンクション`to_yminterval`に指定されたISO 8601 interval文字列の`$intervalYearMonth`	yearmonthInterval	`INTERVAL YEAR TO MONTH`	string	値がSQLファンクション`to_yminterval`に指定されたISO 8601 interval文字列の`$intervalYearMonth`
2つのフィールド: フィールド`$vector`。値は、要素が数値または文字列`"Nan"`、`"Inf"`および`"-Inf"` (非数値および無限値を表す)である配列です。フィールド`$vectorElementType`。文字列値`"float32"`または`"float64"`のいずれかです。これらはそれぞれ、IEEE 32ビットおよびIEEE 64ビットの数値に対応しています。	vector	`VECTOR`	数値の配列	2つのフィールド: フィールド`$vector`。値は、要素が数値または文字列`"Nan"`、`"Inf"`および`"-Inf"` (非数値および無限値を表す)である配列です。フィールド`$vectorElementType`。文字列値`"float32"`または`"float64"`のいずれかです。

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

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

関連トピック

親トピック: JSONデータ型

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

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

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

ノート:

vector型のJSON言語スカラー値は、まず数値のJSON配列に変換されてから比較またはソートされます。結果の配列が比較またはソートされる値です。

ノート:

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型のソート(比較)の順序は次のとおりです。^脚注6

スカラー値は、非スカラー値の前に(キーワード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の拡張オブジェクトから導出された値です。
vector型ファミリ: type()は"vector"を返します。^脚注6

関連トピック

関連項目:

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

親トピック: JSONデータ型

2.2.7 SQL値とJSONデータ型値の比較

JSON型値をJSON以外の型のSQL値と比較するとき、同じファミリの2つのJSON型値(数値など)を比較するときと同じルールが適用されます。ただし、SQL型がそのファミリのJSON言語型のいずれかに対応しているという前提です。

(2つのJSON型値の比較については、「JSONデータ型の値の比較とソート」を参照してください。)

これは、JSON値とSQL値の間で直接行われる唯一の有用な比較です。他のこのような比較ではすべて、単にfalseが返されるか、(例外的に)エラーが発生します。

たとえば、列DATAがJSON型であるとすると、これはJSON型の数値とSQL NUMBER値の有用な比較です。

SELECT c.data FROM customers
  ORDER BY c.data.address.zip <= 12345;

この問合せは、実際には次の問合せと同等です:

SELECT c.data FROM customers
  ORDER BY c.data.address.zip.numberOnly() = 12345;

このような比較をtrueにできるのは、JSON数値のみです。フィールドzipの値が数値でない場合、つまり数値ファミリに属していない場合、比較はfalseを返します。たとえば、zipが文字列"314"の場合、比較はfalseになります。文字列"314"を数値314に変換する型変換は自動的に実行されません。

比較するSQL値がJSON言語スカラー型に対応する型でない場合、エラーが発生します。

たとえば、SDO_GEOMETRY値はJSON値と直接比較できません:

ERROR at line 1:ORA-00932: expression is of data type
MDSYS.SDO_GEOMETRY, which is incompatible with expected data type JSON

比較対象のJSON値が非スカラー(オブジェクトまたは配列)の場合、比較はSQL値に関係なくfalseを返します。

JSON型値のJSON言語型が不明で、比較対象のSQL値と互換性のある型に(可能なときは)変換する場合、型変換項目メソッドを使用できます(「データ型変換の項目メソッド」を参照してください)。

たとえば、この問合せでは、項目メソッドnumber()を使用して、RETURNING NUMBER句を指定したファンクションjson_valueと同様にフィールドzipの値を解釈するため、zip JSON文字列"314"はJSON数値314に変換されます(比較はtrueです)。

SELECT data FROM customers
  ORDER BY c.data.address.zip.number() = 12345

ヒント:

JSONデータ・ガイドを使用して、不明なJSONデータ内の値のデータ型を特定できます。

比較対象のSQL値がJSON値を表す文字列である場合、JSONコンストラクタを使用してJSON型値に変換できます。これにより、スカラーか非スカラーかに関係なく、どのJSON値でも比較できます。(実際には、これはJSON値とSQL値を比較するケースではありません。)

たとえば、この問合せでは、JSON型のフィールドzipの値を、コンストラクタがSQL文字列'12345'から解析したJSON型の数値12345と比較します。

SELECT c.data FROM customers
  ORDER BY c.data.address.zip <= JSON('12345');

また、この問合せでは、フィールドaddressの値を、次に示すリテラルJSONオブジェクトと比較します:

SELECT c.data FROM customers
  ORDER BY c.data.address <=
    JSON('{"street"  : "200 Sporting Green",
           "city"    : "South San Francisco",
           "state"   : "CA",
           "zipCode" : 99236');

比較対象のJSON値がテキスト(JSON型ではない)の場合、比較ではSQL文字列(VARCHAR2)として扱われます。

たとえば、列textualjsonが型VARCHAR2で、フィールドzipの値がJSON数値314の場合、314が12345より辞書的に大きいため、この比較はfalseです。

SELECT c.textualjson FROM customers
  ORDER BY c.textualjson.address.zip <= '12345';

JSON数値はSQL文字列'314'に変換され、SQL文字列'12345'と比較されます(数値比較ではなく文字列比較)。

関連トピック

親トピック: JSONデータ型

脚注の凡例

脚注1: キーワードASCII、PRETTY、ORDERED、TRUNCATEおよびEXTENDEDはOracleの拡張機能であり、SQL/JSON標準には含まれません。
脚注6: スカラー・ベクター値はスカラーとして処理されないことに注意してください。かわりに、比較目的では数値の配列として処理されます。