JSON条件

JavaScript Object Notation(JSON)条件によって、次のようにJSONデータをテストできます。

IS JSON条件によって、式が構文的に正しいJSONデータかどうかをテストできます。
JSON_EXISTS条件によって、指定されたJSON値がJSONデータにあるかどうかをテストできます。
JSON_TEXTCONTAINS条件によって、指定された文字列がJSONプロパティ値にあるかどうかをテストできます。

JSON_condition::=

「図json_condition.gif」の説明

注意:

Oracle Database 12cリリース1(12.1.0.2)以降、JSON条件を使用できます。

IS JSON条件

この条件を使用して、式が構文的に正しい整形式のJSONデータかどうかをテストします。

IS JSONを指定すると、この条件は式が整形式のJSONデータである場合にTRUEを戻し、式が整形式のJSONデータでない場合にFALSEを戻します。
IS NOT JSONを指定すると、この条件は式が整形式のJSONデータでない場合にTRUEを戻し、式が整形式のJSONデータである場合にFALSEを戻します。

is_JSON_condition::=

図is_json_condition.gifの説明

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_EXISTS条件を使用して、指定されたJSON値がJSONデータにあるかどうかをテストします。この条件は、JSON値が存在する場合にTRUEを戻し、JSON値が存在しない場合にFALSEを戻します。

JSON_exists_condition::=

図json_exists_condition.gifの説明

JSON_path_expression::=

図json_path_expression.gifの説明

object_step::=

図object_step.gifの説明

array_step::=

図array_step.gifの説明

JSON_exists_on_error_clause::=

図json_exists_on_error_clause.gifの説明

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_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_condition.gifの説明

JSON_path_expression::=

図json_path_expression.gifの説明

(JSON_TEXTCONTAINSはarray_stepをサポートしていません)

object_step::=

図object_step.gifの説明

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オブジェクトでない場合、オブジェクト・ステップは一致しません。

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}}}