SQL For JSON条件

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

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

JSON_condition::=

図json_condition.epsの説明

IS JSON条件

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

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

is_JSON_condition::=

図is_json_condition.epsの説明

exprは、評価の対象となるJSONデータを指定するために使用します。テキスト・リテラルを評価する式を指定します。exprが列である場合、列のデータ型はVARCHAR2、CLOBまたはBLOBのいずれかである必要があります。exprがnullまたは長さゼロのテキスト・リテラルを評価する場合、この条件はUNKNOWNを戻します。
exprがデータ・タイプBLOBの列の場合、FORMAT JSONを指定する必要があります。
STRICTを指定すると、この条件は厳密なJSON構文のみを整形式のJSONデータとみなします。LAXを指定すると、この条件は緩いJSON構文のみを整形式のJSONデータとみなします。デフォルトはLAXです。厳密なJSON構文および緩いJSON構文の詳細は、Oracle Database JSON開発者ガイドを参照してください。
WITH UNIQUE KEYSを指定すると、キー名が各オブジェクト内で一意である場合のみ、この条件は整形式のJSONデータとみなします。WITHOUT UNIQUE KEYSを指定すると、キー名がオブジェクト内で重複する場合、この条件は整形式のJSONデータとみなします。WITHOUT UNIQUE KEYSテストは、WITH UNIQUE KEYSテストより高速に実行されます。デフォルトはWITHOUT UNIQUE KEYSです。

例

厳密なJSON構文または緩い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の使用方法: 例

次の文は、表j_purchaseorderを作成し、JSONデータを列po_documentに格納します。この文は、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_EQUAL条件

構文

図json_equal_condition.epsの説明

目的

Oracle SQL条件JSON_EQUALは2つのJSON値を比較してtrueを戻します。2つの値が同じでない場合、falseを返します。入力値は有効なJSONデータである必要があります。

この比較では、意味のない空白と、意味のないオブジェクト・メンバーの順序は無視されます。たとえば、JSONオブジェクトに同じメンバーがある場合、その順序に関係なく、これらのオブジェクトは等しくなります。

比較された2つの入力のいずれかに1つ以上のフィールドの重複がある場合、JSON_EQUALによって戻される値は特定されません。

JSON_EQUALでは、ERROR ON ERROR、FALSE ON ERRORおよびTRUE ON ERRORがサポートされています。デフォルトはFALSE ON ERRORです。エラーの典型的な例は、入力式が有効なJSONでない場合です。

例

次の文はTRUEを戻します。

JSON_EQUAL('{}', '{ }')

JSON_EQUAL('{a:1, b:2}', '{b:2 , a:1 }')

次の文はFALSEを返します。

JSON_EQUAL('{a:"1"}', '{a:1 }') -> FALSE

次の文は、ORA-40441 JSON構文エラーになります

JSON_EQUAL('[1]', '[}' ERROR ON ERROR)

JSON_EXISTS条件

SQL/JSON条件JSON_EXISTSを使用して、指定されたJSON値がJSONデータにあるかどうかをテストします。この条件は、JSON値が存在する場合にTRUEを戻し、JSON値が存在しない場合にFALSEを戻します。

JSON_exists_condition::=

図json_exists_condition.epsの説明

(JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照)

JSON_passing_clause::=

図json_passing_clause.epsの説明

JSON_exists_on_error_clause::=

図json_exists_on_error_clause.epsの説明

JSON_exists_on_empty_clause::=

図json_exists_on_empty_clause.epsの説明

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_basic_path_expression

この句を使用して、SQL/JSONパス式を指定します。この条件はパス式を使用してexprを評価し、パス式と一致する(パス式を満たす)JSON値があるかどうかを判断します。パス式はテキスト・リテラルである必要がありますが、パス式には、JSON_passing_clauseによって値がパス式に渡される変数を含めることができます。JSON_basic_path_expressionのセマンティクスの詳細は、Oracle Database JSON開発者ガイドを参照してください。

JSON_passing_clause

この句を使用して、値をパス式に渡します。exprには、VARCHAR2、NUMBER、BINARY_DOUBLE、DATE、TIMESTAMPまたはTIMESTAMP WITH TIME ZONEデータ型の値を指定します。exprの評価結果は、JSON_basic_path_expressionの対応する識別子にバインドされます。

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を戻します。これはデフォルトです。

JSON_exists_on_empty_clause

この句を使用して、JSONデータがSQL/JSONパス式を使用して評価されるときに一致が見つからない場合にこのファンクションで戻される値を指定します。この句により、JSON_exists_on_error_clauseで指定された結果とは異なる、このタイプのエラーに対する結果を指定できます。

次の句を指定できます。

NULL ON EMPTY - 一致が見つからない場合にNULLを戻します。
ERROR ON EMPTY - 一致が見つからない場合に適切なOracleエラーを戻します。
DEFAULT literal ON EMPTY - 一致が見つからない場合にliteralを戻します。literalのデータ型は、このファンクションにより戻される値のデータ型と一致する必要があります。

この句を省略すると、JSON_exists_on_error_clauseによって、一致が見つからない場合に戻される値が決まります。

例

次の文は、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条件

SQL/JSON条件JSON_TEXTCONTAINSを使用して、指定された文字列がJSONプロパティ値にあるかどうかをテストします。この条件を使用して、特定のワードまたは数値でJSONデータをフィルタ処理できます。

この条件では次の引数を使用します。

JSONデータを含む表またはビューの列。JSONデータでの使用に特化して設計されているOracle TextデータであるJSON検索索引を列に定義する必要があります。列のJSONデータの各行は、JSONドキュメントといいます。
SQL/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.epsの説明

(JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照)

column

テストするJSONデータを含む表またはビューの列の名前を指定します。列のデータ型はVARCHAR2、CLOBまたはBLOBである必要があります。JSONデータでの使用に特化して設計されているOracle TextデータであるJSON検索索引を列に定義する必要があります。列の値がnullまたは長さゼロのテキスト・リテラルである場合、この条件はUNKNOWNを戻します。

列の値が厳密な構文または緩い構文を使用した整形式のJSONデータのテキスト・リテラルでない場合、この条件はFALSEを戻します。

JSON_basic_path_expression

この句を使用して、SQL/JSONパス式を指定します。この条件はパス式を使用してcolumnを評価し、パス式と一致する(パス式を満たす)JSON値があるかどうかを判断します。パス式はテキスト・リテラルである必要があります。JSON_basic_path_expressionのセマンティクスの詳細は、Oracle Database 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}}}