JSON_TABLE (SQL)

構文

InterSystems IRIS® SQL

JSON_TABLE( json-value, json-path col-mapping )

InterSystems IRIS Cloud Document Service

JSON_TABLE( collection-name FORMAT COLLECTION, json-path col-mapping )

説明

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

collection-name

InterSystems IRIS Cloud DocumentOpens in a new tab では、コレクションと組み合わせて JSON_TABLE 関数が使用されます。コレクションには、1 つ以上の JSON ドキュメントの JSON データが含まれます。1 つ以上の JSON ドキュメントを導入環境にアップロードしてコレクションに追加した後、JSON_TABLE を指定して、そのコレクションをソースとして使用できます。

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" で始まる要素のみを選択します。
@	フィルタ操作の条件内で使用され、現在の項目を表します。

例

InterSystems IRIS SQL

以下の例は、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 人のstateとnameを選択します。具体的には、結果セットには最初の 5 つの州がアルファベット順に含まれ、各州の上院議員もアルファベット順に返されます。列のマッピングで指定された sortname 列は、最終的に結果セットには返されませんが、この列を使用して、アルファベット順に並べ替えることができます。さらにこの例では、以前、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"}'),
  '$.content.objects'
    COLUMNS ( name VARCHAR(100) PATH '$.person.name',
              sortname VARCHAR(100) PATH '$.person.sortname',
              state VARCHAR(50) PATH '$.state' )
  ) as jt
  ORDER BY state, sortname

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

InterSystems IRIS Cloud Document サービス

以下の例では、NBATeams と NBAPlayers の 2 つのコレクションを結合し、Boston Celtics でプレイする NBA 選手の名前と姓を選択します。これらのコレクションは、Cloud Portal の [ファイルの追加と管理] ページで導入に JSON ファイルを追加してから、[コレクションのインポート] ページで、ファイルに含まれる JSON データをコレクションにインポートすることで作成されています。

SELECT firstName, lastName
  FROM JSON_TABLE( NBATeams FORMAT COLLECTION, '$'
                   COLUMNS ( teamId BIGINT path '$.teamId',
                             teamName VARCHAR(50) path '$.teamName' )
                  ) AS Teams
  JOIN JSON_TABLE( NBAPlayers FORMAT COLLECTION, '$'
                   COLUMNS ( teamId BIGINT path '$.teamId',
                             firstName VARCHAR(50) path '$.firstName',
                             lastName VARCHAR(50) path '$.lastName' )
                 ) AS Players
  ON teams.teamId = players.teamId
  WHERE teams.teamName = 'Boston Celtics'
  ORDER BY lastName, firstName