JavaScript Object Notation(JSON)条件によって、次のようにJSONデータをテストできます。
IS JSON条件によって、式が構文的に正しいJSONデータかどうかをテストできます。
JSON_EXISTS条件によって、指定されたJSON値がJSONデータにあるかどうかをテストできます。
JSON_TEXTCONTAINS条件によって、指定された文字列がJSONプロパティ値にあるかどうかをテストできます。
JSON_condition::=
注意: Oracle Database 12cリリース1(12.1.0.2)以降、JSON条件を使用できます。 |
この条件を使用して、式が構文的に正しい整形式のJSONデータかどうかをテストします。
IS
JSON
を指定すると、この条件は式が整形式のJSONデータである場合にTRUE
を戻し、式が整形式のJSONデータでない場合にFALSE
を戻します。
IS
NOT
JSON
を指定すると、この条件は式が整形式のJSONデータでない場合にTRUE
を戻し、式が整形式のJSONデータである場合にFALSE
を戻します。
is_JSON_condition::=
expr
は、評価の対象となるJSONデータを指定するために使用します。テキスト・リテラルを評価する式を指定します。expr
が列である場合、列のデータ型はVARCHAR2
、CLOB
またはBLOB
のいずれかである必要があります。expr
がnullまたは長さゼロのテキスト・リテラルを評価する場合、この条件はUNKNOWN
を戻します。
expr
がデータ・タイプBLOB
の列の場合、FORMAT
JSON
を指定する必要があります。
STRICT
を指定すると、この条件は厳密なJSON構文のみを整形式のJSONデータとみなします。LAX
を指定すると、この条件は緩いJSON構文のみを整形式のJSONデータとみなします。デフォルトはLAX
です。厳密なJSON構文および緩いJSON構文の詳細は、『Oracle XML DB開発者ガイド』を参照してください。
WITH
UNIQUE
KEYS
を指定すると、キー名が各オブジェクト内で一意である場合のみ、この条件は整形式のJSONデータとみなします。WITHOUT
UNIQUE
KEYS
を指定すると、キー名がオブジェクト内で重複する場合、この条件は整形式のJSONデータとみなします。WITHOUT
UNIQUE
KEYS
テストは、WITH
UNIQUE
KEYS
テストより高速に実行されます。デフォルトはWITHOUT
UNIQUE
KEYS
です。
例
STRICTまたはLAX JSON構文のテスト: 例 次の文は、列col1
を使用した表t
を作成します。
CREATE TABLE t (col1 VARCHAR2(100));
次の文は、値を表t
の列col1
に挿入します。
INSERT INTO t VALUES ( '[ "LIT192", "CS141", "HIS160" ]' ); INSERT INTO t VALUES ( '{ "Name": "John" }' ); INSERT INTO t VALUES ( '{ "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } }'); INSERT INTO t VALUES ( '{ "isEnrolled" : true }' ); INSERT INTO t VALUES ( '{ "isMatriculated" : False }' ); INSERT INTO t VALUES (NULL); INSERT INTO t VALUES ('This is not well-formed JSON data');
次の文は、表t
を問い合せて、整形式のJSONデータであるcol1
値を戻します。STRICT
およびLAX
キーワードが指定されていないため、この例はデフォルトのLAX
設定を使用します。このため、この問合せは、厳密なJSON構文または緩いJSON構文を使用する値を戻します。
SELECT col1 FROM t WHERE col1 IS JSON; COL1 -------------------------------------------------- [ "LIT192", "CS141", "HIS160" ] { "Name": "John" } { "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } } { "isEnrolled" : true } { "isMatriculated" : False }
次の文は、表t
を問い合せて、整形式のJSONデータであるcol1
値を戻します。この例は、STRICT
設定を指定します。このため、この問合せは、厳密なJSON構文を使用する値のみ戻します。
SELECT col1 FROM t WHERE col1 IS JSON STRICT; COL1 -------------------------------------------------- [ "LIT192", "CS141", "HIS160" ] { "Name": "John" } { "isEnrolled" : true }
次の文は表t
を問い合せて緩いJSON構文を使用するcol1
値を戻しますが、厳密なJSON構文を使用するcol1
値を省略します。このため、この問合せは、緩いJSON構文で使用できる例外を含む値のみ戻します。
SELECT col1 FROM t WHERE col1 IS NOT JSON STRICT AND col1 IS JSON LAX; COL1 -------------------------------------------------- { "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } } { "isMatriculated" : False }
一意のキーのテスト 次の文は、列col1
を使用した表t
を作成します。
CREATE TABLE t (col1 VARCHAR2(100));
次の文は、値を表t
の列col1
に挿入します。
INSERT INTO t VALUES ('{a:100, b:200, c:300}'); INSERT INTO t VALUES ('{a:100, a:200, b:300}'); INSERT INTO t VALUES ('{a:100, b : {a:100, c:300}}');
次の文は、表tを問い合せて各オブジェクト内に一意のキー名を持つ整形式のJSONデータであるcol1
値を戻します。
SELECT col1 FROM t WHERE col1 IS JSON WITH UNIQUE KEYS; COL1 --------------------------- {a:100, b:200, c:300} {a:100, b : {a:100, c:300}}
キー名a
が2回表示され、2つの異なるオブジェクトにあるため、2番目の行が戻されます。
次の文は、表t
を問い合せて、各オブジェクト内に一意のキー名があるかどうかに関係なく整形式のJSONデータであるcol1
値を戻します。
SELECT col1 FROM t WHERE col1 IS JSON WITHOUT UNIQUE KEYS; COL1 --------------------------- {a:100, b:200, c:300} {a:100, a:200, b:300} {a:100, b : {a:100, c:300}}
CHECK制約としてIS JSONを使用: 例 次の文は、列po_document
にJSONデータを格納する表j_purchaseorder
を作成します。この文は、CHECK制約としてIS
JSON
条件を使用し、整形式のJSONのみ列po_document
に格納します。
CREATE TABLE j_purchaseorder (id RAW (16) NOT NULL, date_loaded TIMESTAMP(6) WITH TIME ZONE, po_document CLOB CONSTRAINT ensure_json CHECK (po_document IS JSON));
JSON_EXISTS
条件を使用して、指定されたJSON値がJSONデータにあるかどうかをテストします。この条件は、JSON値が存在する場合にTRUE
を戻し、JSON値が存在しない場合にFALSE
を戻します。
JSON_exists_condition::=
object_step::=
array_step::=
JSON_exists_on_error_clause::=
expr
この句は、評価の対象となるJSONデータを指定するために使用します。expr
では、テキスト・リテラルを評価する式を指定します。expr
が列である場合、列のデータ型はVARCHAR2
、CLOB
またはBLOB
のいずれかである必要があります。expr
がnullまたは長さゼロのテキスト・リテラルを評価する場合、この条件はUNKNOWN
を戻します。
expr
が厳密なまたは緩い構文を使用した整形式のJSONデータのテキスト・リテラルでない場合、この条件はデフォルトでFALSE
を戻します。JSON_exists_on_error_clause
を使用して、このデフォルトの動作をオーバーライドできます。JSON_exists_on_error_clauseを参照してください。
FORMAT JSON
expr
がデータ・タイプBLOB
の列の場合、FORMAT
JSON
を指定する必要があります。
JSON_path_expression
この句を使用して、JSONパス式を指定します。この条件はパス式を使用してexpr
を評価し、パス式と一致する(パス式を満たす)JSON値があるかどうかを判断します。パス式はテキスト・リテラルである必要があります。
パス式は、expr
で指定される式であるコンテキスト項目を表すドル記号($
)から始める必要があります。ドル記号の後、オブジェクト・ステップや配列ステップの可能性があるゼロ以上のステップが続きます。この条件は、コンテキスト項目に対するパス式の最初のステップと一致するかどうかを試行します。最初のステップが一致した場合、この条件は最初のステップと一致したJSON値に対して2番目のステップと一致するかどうかを試行します。2番目のステップが一致した場合、この条件は2番目のステップと一致したJSON値に対して3番目のステップと一致するかどうかを試行し、以降も同様に続きます。最後のステップが一致した場合、この条件はTRUE
を戻します。パス式のいずれかのステップが一致しなかった場合、この条件はFALSE
を戻します。ドル記号とゼロのステップで構成されるパス式('$'
)は、全体のコンテキスト項目と一致します。
object_step この句を使用して、オブジェクト・ステップを指定します。
simple_name
またはcomplex_name
を使用して、プロパティ名を指定します。このプロパティ名のメンバーが評価されるJSONオブジェクトにある場合、そのメンバーのプロパティ値が一致するオブジェクト・ステップになります。それ以外の場合、一致しないオブジェクト・ステップになります。両方のタイプの名前は大/小文字を区別します。このため、アルファベットの大/小文字がオブジェクト・ステップおよびJSONデータで一致する場合のみ、一致の結果になります。
simple_name
は、英数字のみで構成され、最初の文字はアルファベット文字にする必要があります。complex_name
は、英数字のみで構成され、最初の文字は英数字にする必要があります。complex_name
は、二重引用符で囲む必要があります。
ワイルド・カード記号であるアスタリスク(*
)を使用して、すべてのプロパティ名を指定します。評価されるJSONオブジェクトに少なくとも1つのメンバーが含まれる場合、オブジェクト・ステップはすべてのメンバーの値と一致する結果になります。それ以外の場合、一致しないオブジェクト・ステップになります。
オブジェクト・ステップをJSON配列に適用すると、配列が暗黙的にアンラップされ、配列の要素がオブジェクト・ステップを使用して評価されます。これは、JSONパス式解除といいます。詳細は、『Oracle XML DB開発者ガイド』を参照してください。
評価されるJSON値がJSONオブジェクトでない場合、オブジェクト・ステップは一致しません。
array_step この句を使用して、配列ステップを指定します。
integer
を使用して、JSON配列の索引integer
の要素を指定します。integer
TO
integer
を使用して、2つのinteger
索引値の間(これらの値を含む)の要素の範囲を指定します。指定された要素が評価されるJSON配列にある場合、配列ステップはそれらの要素と一致する結果になります。それ以外の場合、一致しない配列ステップになります。JSON配列の最初の要素は索引0です。
ワイルド・カード記号であるアスタリスク(*
)を使用して、JSON配列のすべての要素を指定します。評価されるJSON配列に少なくとも1つの要素が含まれる場合、JSON配列のすべての要素が一致する配列ステップになります。それ以外の場合、一致しない配列ステップになります。
評価されるJSONデータがJSON配列でない場合、データが暗黙的に配列にラップされ、配列ステップを使用して評価されます。これは、JSONパス式解除といいます。詳細は、『Oracle XML DB開発者ガイド』を参照してください。
JSON_exists_on_error_clause
この句を使用して、expr
が整形式のJSONデータでない場合にこの条件で戻される値を指定します。
次の句を指定できます。
ERROR
ON
ERROR
- expr
が整形式のJSONデータでない場合、適切なOracleエラーを戻します。
TRUE
ON
ERROR
- expr
が整形式のJSONデータでない場合にTRUE
を戻します。
FALSE
ON
ERROR
- expr
が整形式のJSONデータでない場合にFALSE
を戻します。これはデフォルトです。
例
次の文は、name
列を使用した表t
を作成します。
CREATE TABLE t (name VARCHAR2(100));
次の文は、値を表t
の列name
に挿入します。
INSERT INTO t VALUES ('[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]'); INSERT INTO t VALUES ('[{first:"Mary"}, {last:"Jones"}]'); INSERT INTO t VALUES ('[{first:"Jeff"}, {last:"Williams"}]'); INSERT INTO t VALUES ('[{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]'); INSERT INTO t VALUES (NULL); INSERT INTO t VALUES ('This is not well-formed JSON data');
次の文は表t
の列name
を問い合せて、最初の要素がプロパティ名first
のオブジェクトである配列で構成されるJSONデータを戻します。ON
ERROR
句は指定されません。このため、JSON_EXISTS
条件は、整形式のJSONデータでない値に対してFALSE
を戻します。
SELECT name FROM t WHERE JSON_EXISTS(name, '$[0].first'); NAME -------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Mary"}, {last:"Jones"}] [{first:"Jeff"}, {last:"Williams"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]
次の文は表t
の列name
を問い合せて、2番目の要素がプロパティ名middle
のオブジェクトである配列で構成されるJSONデータを戻します。ON
ERROR
句は指定されません。このため、JSON_EXISTS
条件は、整形式のJSONデータでない値に対してFALSE
を戻します。
SELECT name FROM t WHERE JSON_EXISTS(name, '$[1].middle'); NAME -------------------------------------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]
次の文は前の文と似ていますが、TRUE
ON
ERROR
句が指定されている点が異なります。このため、JSON_EXISTS
条件は、整形式のJSONデータでない値に対してTRUE
を戻します。
SELECT name FROM t WHERE JSON_EXISTS(name, '$[1].middle' TRUE ON ERROR); NAME -------------------------------------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}] This is not well-formed JSON data
次の文は表t
の列name
を問い合せて、プロパティ名last
のオブジェクトである要素を含む配列で構成されるJSONデータを戻します。ワイルドカード記号(*
)が配列索引に指定されます。このため、配列の索引番号に関係なく、問合せは、そのようなオブジェクトを含む配列を戻します。
SELECT name FROM t WHERE JSON_EXISTS(name, '$[*].last'); NAME -------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Mary"}, {last:"Jones"}] [{first:"Jeff"}, {last:"Williams"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]
JSON_TEXTCONTAINS
条件を使用して、指定された文字列がJSONプロパティ値にあるかどうかをテストします。この条件を使用して、特定のワードまたは数値でJSONデータをフィルタ処理できます。
この条件では次の引数を使用します。
JSONデータを含む表またはビューの列。JSONデータでの使用に特化して設計されているOracle TextデータであるJSON検索索引を列に定義する必要があります。列のJSONデータの各行は、JSONドキュメントといいます。
JSONパス式。ドキュメント内の特定のJSONオブジェクトと一致するかどうかの試行で、パス式が各JSONドキュメントに適用されます。パス式にはJSONオブジェクト・ステップのみ含むことができ、JSON配列ステップを含むことはできません。
文字列。この条件は、配列値を含む一致したJSONオブジェクトのすべての文字列および数値のプロパティ値内の文字列を検索します。文字列は、プロパティ値の個別のワードとして存在する必要があります。たとえば、'beth'を検索すると、文字列のプロパティ値"beth smith"と一致しますが、"elizabeth smith"とは一致しません。'10'を検索すると、数値のプロパティ値10または文字列のプロパティ値"10 main street"と一致しますが、数値のプロパティ値110または文字列のプロパティ値"102 main street"とは一致しません。
この条件は、一致する場合にTRUE
を戻し、一致しない場合にFALSE
を戻します。
JSON_textcontains_condition::=
(JSON_TEXTCONTAINS
はarray_step
をサポートしていません)
object_step::=
column
テストするJSONデータを含む表またはビューの列の名前を指定します。列のデータ型はVARCHAR2
、CLOB
またはBLOB
である必要があります。JSONデータでの使用に特化して設計されているOracle TextデータであるJSON検索索引を列に定義する必要があります。列の値がnullまたは長さゼロのテキスト・リテラルである場合、この条件はUNKNOWN
を戻します。
列の値が厳密な構文または緩い構文を使用した整形式のJSONデータのテキスト・リテラルでない場合、この条件はFALSE
を戻します。
JSON_path_expression
この句を使用して、JSONパス式を指定します。この条件はパス式を使用してcolumn
を評価し、パス式と一致する(パス式を満たす)JSON値があるかどうかを判断します。パス式はテキスト・リテラルである必要があります。
パス式は、column
であるコンテキスト項目を表すドル記号($
)から始める必要があります。ドル記号の後、ゼロ以上のオブジェクト・ステップが続きます。
この条件は、コンテキスト項目に対するパス式の最初のステップと一致するかどうかを試行します。最初のステップが一致した場合、この条件は最初のステップと一致したJSON値に対して2番目のステップと一致するかどうかを試行します。2番目のステップが一致した場合、この条件は2番目のステップと一致したJSON値に対して3番目のステップと一致するかどうかを試行し、以降も同様に続きます。パス式のいずれかのステップが一致しなかった場合、この条件はFALSE
を戻します。最後のステップが一致し、一致した値にstring
が含まれる場合、この条件はTRUE
を戻します。それ以外の場合、この条件はFALSE
を戻します。
ドル記号とゼロのオブジェクト・ステップで構成されるパス式('$'
)は、全体のコンテキスト項目と一致します。
object_step この句を使用して、オブジェクト・ステップを指定します。
simple_name
またはcomplex_name
を使用して、プロパティ名を指定します。このプロパティ名のメンバーが評価されるJSONオブジェクトにある場合、そのメンバーのプロパティ値が一致するオブジェクト・ステップになります。それ以外の場合、一致しないオブジェクト・ステップになります。両方のタイプの名前は大/小文字を区別します。このため、アルファベットの大/小文字がオブジェクト・ステップおよびJSONデータで一致する場合のみ、一致の結果になります。
simple_name
は、英数字のみで構成され、最初の文字はアルファベット文字にする必要があります。complex_name
は、英数字のみで構成され、最初の文字は英数字にする必要があります。complex_name
は、二重引用符で囲む必要があります。
ワイルド・カード記号であるアスタリスク(*
)を使用して、すべてのプロパティ名を指定します。評価されるJSONオブジェクトに少なくとも1つのメンバーが含まれる場合、オブジェクト・ステップはすべてのメンバーの値と一致する結果になります。それ以外の場合、一致しないオブジェクト・ステップになります。
オブジェクト・ステップをJSON配列に適用すると、配列が暗黙的にアンラップされ、配列の要素がオブジェクト・ステップを使用して評価されます。これは、JSONパス式解除といいます。詳細は、『Oracle XML DB開発者ガイド』を参照してください。
評価されるJSON値がJSONオブジェクトでない場合、オブジェクト・ステップは一致しません。
string
この条件は、string
で指定された文字列を検索します。文字列は一重引用符で囲みます。
例
次の文は、family_doc
列を使用した表families
を作成します。
CREATE TABLE families (family_doc VARCHAR2(200));
次の文は、列family_doc
のJSON検索索引を作成します。
CREATE INDEX ix ON families(family_doc) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');
次の文は、ファミリを示すJSONドキュメントを列family_doc
に挿入します。
INSERT INTO families VALUES ('{family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}'); INSERT INTO families VALUES ('{family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}}'); INSERT INTO families VALUES ('{family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}');
次の文は、トランザクションをコミットします。
COMMIT;
次の問合せは、ドキュメントのプロパティ値に10
を含むJSONドキュメントを戻します。
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}} {family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}} {family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}
次の問合せは、id
のプロパティ値に10を含むJSONドキュメントを戻します。
SELECT family_doc FROM families where json_textcontains(family_doc, '$.family.id', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}
次の問合せは、ages
プロパティの値の配列に10を含むJSONドキュメントを戻します。
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$.family.ages', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}}
次の問合せは、address
プロパティ値に10を含むJSONドキュメントを戻します。
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$.family.address', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}} {family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}
次の問合せは、apt
プロパティ値に10を含むJSONドキュメントを戻します。
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$.family.address.apt', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}