Skip to main content

This documentation is for an older version of this product. See the latest version of this content.Opens in a new tab

JSON_TABLE (SQL)

構文

説明

JSON_TABLE 関数は、JSONOpens in a new tab 値を列にマップすることにより、SQL クエリで使用できるテーブルを返します。JSON 値から列へのマッピングは、SQL/JSON パス言語式として記述されます。

テーブル値関数として、JSON_TABLE は、JSON 値に格納されたデータにアクセスするため、SELECT 文の FROM 節で使用できるテーブルを返します。1 つの FROM 節から複数の JSON_TABLE 呼び出しを行うことができ、他のテーブル値関数と一緒に使用できます。

引数

json-value

json-value 引数は、リテラル JSON 文字列、JSON 文字列を返す関数、JSON 値を含む列の 3 つの形式のいずれかを取ることができます。列を指定している場合、FROM 節にその列が含まれるテーブルをリストする必要があります。

以下の例は、有効な json-values を示します。

'[{"month":"November"}, {"month":"May"}, {"month":"January"}]' // a JSON string

%Net.GetJson(<JSON-url>,<SSL-configuration>) // a function that returns a JSON string

Profile.Address // a column that contains JSON string, where the 
                   Profile table is listed in the FROM clause

json-path

json-path 引数は、json-value から抽出する値を指定する SQL/JSON パス言語式です。json-value 全体を指定するには、'$' を使用します。この引数は、JSON ドキュメントをフィルタ処理して、類似したフィールドを持つ JSON オブジェクトの特定のサブセットを選択するために使用します。このサブセットの個々の要素は、JSON_TABLE によって返されるテーブル内の行に対応します。

構文の詳細な説明は、"SQL/JSON パス言語式の使用" を参照してください。

json-path 引数は、SQL 標準に従って、スペースによってのみ、col-mapping 引数と区切られることに注意してください。

col-mapping

col-mapping 引数は、COLUMNS 節を使用して JSON_TABLE によって返されるテーブルの列を定義します。COLUMNS 節の各エントリには、列名、型、および json-path で識別されるパスから始まる、列に含める値を特定する SQL/JSON パス言語式が必要です。

json-path 引数は、JSON ドキュメントからオブジェクトのサブセットを返すパスにアクセスします。サブセット内のオブジェクトの特定のフィールドの選択には、col-mapping 内の JSON パス式を使用する必要があります。

定義できる列マッピングの数に制限はありません。

SQL/JSON パス言語式の構文の詳細な説明は、"SQL/JSON パス言語式の使用" を参照してください。

SQL/JSON パス言語式の使用

SQL/JSON パス言語式は、JSON ドキュメントを介した目的の値のセットへのパスを定義する一連の演算子です。JSON_TABLE のコンテキスト内で、2 つの別個の SQL/JSON パス言語式 (1 つは json-path からのもの、もう 1 つは col-mapping からのもの) が組み合わさり、1 つの列マッピングを定義します。json-path 引数の SQL/JSON パス言語式は、列にマップするフィールドを含む (行にマップする) オブジェクトのセットを定義します。一方、col-mapping 引数の SQL/JSON パス言語式は、列にマップする特定のフィールドを定義します。

使用できる演算を以下のテーブルに示します。

演算子 説明 例
$ JSON 値全体を選択し、すべての SQL/JSON パス言語式を開始します。

$ — JSON ドキュメント全体を選択します。
.<fieldname> JSON オブジェクトからフィールドを選択します。すべてのフィールドを指定するには、* を使用します。

$.number — JSON オブジェクトの数値フィールドを選択します。

$.* — JSON オブジェクトのすべてのフィールドを選択します。
[] JSON 配列から要素の範囲を選択します。すべての要素を示すには、* を使用します。インデックスの範囲を指定するには、x to y 構文を使用します。整数 x は整数 y より小さい値とします。個々のインデックスを指定するには、それらをコンマで区切ります。同じ配列セレクタ内で、範囲と個々のインデックスの両方を指定して、さらに複雑な選択を行うこともできます。

$[*] — JSON 配列のすべての要素を選択します。

$[0 to 5] — JSON 配列の 1 ～ 6 番目の要素を選択します。

$[0,3,5] — JSON 配列の 1 番目、4 番目、6 番目の要素を選択します。

$[0 to 3, 5] — 1 ～ 4 番目の要素と 6 番目の要素を選択します。
?(<filter-expression>) フィルタ操作を開始します。フィルタ式は、括弧で囲んで記述します。有効な条件演算子は、&&、||、!、==、!=、<、<=、>、>=、starts with、exists() です。

$[*]?(@.number > 0) — JSON 配列のすべての要素を選択した後、それらをフィルタ処理して、number フィールド内の値が 0 より大きい要素のみを選択します。

$[*]?(@.order starts with "first") — JSON 配列のすべての要素を選択した後、それらをフィルタ処理して、order フィールド内の値が "first" で始まる要素のみを選択します。
@ フィルタ操作の条件内で使用され、現在の項目を表します。

例

以下の例は、JSON 値から列へのマッピングから my_value フィールドを選択します。これらの列の値は、JSON 値によって与えられます。これは、それぞれに number フィールドを持つ 3 つのオブジェクトで構成される配列です。json-path 引数は、col-mapping 内の SQL/JSON パス言語式が照合されるコンテキスト項目として、JSON 値全体を指定します。col-mapping SQL/JSON パス言語式は、my-value 列が配列内のオブジェクトの number フィールドから値を取得することを指定します。結果は、my_value という名前の 1 つの列と 3 つの行で構成されるテーブルになります。

SELECT my_value FROM JSON_TABLE(
  '[{"number":"two"}, {"number":"three"}, {"number":"four"}]',
  '$'
  COLUMNS ( my_value varchar(20) PATH '$.number' )
)

| my_value |
| -- |
| two |
| three |
| four |

以下の例では、%Net.GetJson()Opens in a new tab を使用して JSON 値を指定し、現在の米国上院議員の最初の 10 人の州と名前を選択します。この例では、以前、InterSystems IRIS® データ･プラットフォームのインスタンスで JSON データにアクセスするために定義された Default と呼ばれる SSL 構成を使用しています。SSL 構成の作成については、"TLS 構成の作成または編集" を参照してください。

SELECT TOP 10 state, name
  FROM JSON_TABLE(%Net.GetJson('https://www.govtrack.us/api/v2/role?current=true&role_type=senator','{"SSLConfiguration":"Default"}'),
  '$.objects'
    COLUMNS ( name VARCHAR(100) PATH '$.person.name',
              sortname VARCHAR(100) PATH '$.person.sortname',
              state VARCHAR(50) PATH '$.person.state' )
  ) as jt
  ORDER BY state, sort_name

| state | name|
| -- | -- |
| AK | Sen. Lisa Murkowski [R-AK] |
| AK | Sen. Dan Sullivan [R-AK] |
| AL | Sen. Katie Britt [R-AL] |
| AL | Sen. Tommy Tuberville [R-AL] |
| AR | Sen. John Boozman [R-AR] |
| AR | Sen. Tom Cotton [R-AR] |
| AZ | Sen. Mark Kelly [D-AZ] |
| AZ | Sen. Kyrsten Sinema [I-AZ] |
| CA | Sen. Alejandro "Alex PAdilla [D-CA] |
| CO | Sen. John Hickenlooper [D-CO] |

以下の例では、最初に新しいテーブル Senators を作成し、次に前の例でアクセスした JSON ドキュメントのデータを、Senators テーブルに挿入します。これで JSON ドキュメントのデータは、SQL を介してネイティブにクエリできます。

CREATE TABLE Senators ( person VARCHAR(100),
                        extra VARCHAR (100),
                        state VARCHAR(2) )

INSERT INTO Senators ( person, extra, state )
  SELECT person, extra, state
    FROM JSON_TABLE(%Net.GetJson('https://www.govtrack.us/api/v2/role?current=true&role_type=senator','{"SSLConfiguration":"Default"}'),
  '$.content.objects'
      COLUMNS ( person VARCHAR(100) PATH '$.person',
                extra VARCHAR(100) PATH '$.extra',
                state VARCHAR(50) PATH '$.state'
      )
    )
)

以下の例では、前の例で作成された Senators テーブルを、ラテラル結合を使ったクエリで使用し、10 人の上院議員、彼らの州、誕生日、住所を選択します。Senators テーブルの列には、person フィールドと extra フィールドの JSON 文字列が含まれているため、JSON_TABLE 関数を 2 回使用してこれらのフィールドの値にアクセスするテーブルを返します。この例では、Senators テーブルがクエリされて state フィールドが指定され、JSON_TABLE 関数内で参照されるため、暗黙的なラテラル結合が採用されています。

SELECT TOP 10 jtp.name, state, jtp.birth_date, jte.address
  FROM Senators as Sen,
       JSON_TABLE(Sen.person, '$'
         COLUMNS ( name VARCHAR(60) path '$.sortname',
                   birth_date VARCHAR(10) path '$.birthday'
         )
       ) as jtp,
       JSON_TABLE(Sen.extra, '$'
         COLUMNS ( address VARCHAR(100) path '$.address' )
       ) as jte

| state | name | birth_date | address |
| -- | -- | -- | -- |
| WA | Cantwell, Maria (Sen.) [D-WA] | 1958-10-13 | 511 Hart Senate Office Building Washington DC 20510 |
| DE | Carper, Thomas (Sen.) [D-DE] | 1947-01-23 | 513 Hart Senate Office Building Washington DC 20510 |
| CA | Padilla, Alejandro “Alex” (Sen.) [D-CA] | 1973-03-22 | 112 Hart Senate Office Building Washington DC 20510 |
| MI | Stabenow, Debbie (Sen.) [D-MI] | 1950-04-29 | 731 Hart Senate OFfice Building |
| WI | Baldwin, Tammy (Sen.) [D-WI] | 1962-02-11 | 709 Hart Senate Office Building Washington DC 20510 |
| TN | Blackburn, Marsha (Sen.) [R-TN] | 1952-06-06 | 357 Dirksen Senate Office Building Washington DC 20510 |
| OH | Brown, Sherrod (Sen.) [D-OH] | 1952-11-09 | 503 Hart Senate Office Building Washington DC 20510 |
| MD | Cardin, Benjamin (Sen.) [D-MD] | 1943-10-05 | 509 Hart Senate Office Building Washington DC 20510 |
| NJ | Menendez, Robert “Bob” (Sen.) [D-NJ] | 1954-01-01 | 528 Hart Senate Office Building Washington DC 20510 |
| VT | Sanders, Bernard “Bernie” (Sen.) [I-VT] | 1941-09-08 | 332 Dirksen Senate Office Building Washington DC 20510 |

以下の例では、テーブル Example.JsonValues を作成し、JSON 値を挿入します。その後、このテーブルが JSON_TABLE を使用してクエリされ、個別の行として JSON 値のフィールドが返されます。

CREATE TABLE Example.JsonValues (numbers VARCHAR(200))

INSERT INTO Example.JsonValues (numbers) VALUES ('[{"number":0},{"number":1},{"number":2}]')

SELECT number FROM Example.JsonValues, JSON_TABLE(JsonValues.numbers, '$' 
  COLUMNS (
    number INTEGER PATH '$.number'
  )
)

| number |
| -- |
| 0 |
| 1 |
| 2 |

以下の例では、JSON_TABLE を使用せずに Example.JsonValues テーブルがクエリされます。

SELECT numbers FROM Example.JsonValues
| numbers |
| -- |
| [{"number":0},{"number":1},{"number":2}] |

以下の例では、Example.JsonValues テーブルをクエリし、JSON_TABLE によって解析された値と基本文字列自体の両方を返します。

SELECT * FROM Example.JsonValues, JSON_TABLE(JsonValues, '$' 
  COLUMNS (
    number INTEGER PATH '$.number'
  )
)

| numbers | number |
| -- | -- |
| [{"number":0},{"number":1},{"number":2}] | 0 |
| [{"number":0},{"number":1},{"number":2}] | 1 |
| [{"number":0},{"number":1},{"number":2}] | 2 |

関連項目


FeedbackOpens in a new tab