JSON_TABLE の使用

JSON_TABLE 表関数は、JSON 文書をリレーショナル表に変換します。

ここでは、以下の表 EMP を処理に使用します。この表には 4 行が含まれ、行ごとに 1 つの JSON オブジェクトがあります。JSON_TABLE 関数を使用して、リレーショナル・データとして扱えるようにデータを取り出します。

CREATE TABLE emp(jsondoc VARCHAR(32000) CCSID 1208);

INSERT INTO emp VALUES 
'{"id":901, "name" : { "first":"John", "last":"Doe" }, "office" : "E-334", 
"phones" : [ { "type":"home", "number":"555-3762" }, 
             { "type":"work", "number":"555-7242" } ], 
"accounts" : [ { "number":"36232"}, { "number":"73263"}] }';

INSERT INTO emp VALUES
'{"id":902, "name" : { "first":"Peter", "last":"Pan" }, "office" : "E-216", 
"phones" : [ { "type":"work", "number":"555-8925" } ], 
"accounts" : [ { "number":"76232"}, {"number":"72963"}] }';

INSERT INTO emp VALUES
'{"id":903, "name" : { "first":"Mary", "last":"Jones" }, "office" : "E-739", 
"phones" : [ { "type":"work", "number":"555-4311" }, 
             { "type":"home", "number":"555-6312" } ], }';

INSERT INTO emp VALUES
'{"id":904, "name" : { "first":"Sally", "last":"Smith" } }';

JSON_TABLE 関数は、以下の 3 つのパーツで構成されます。

分解される JSON オブジェクト。
JSON オブジェクトからゼロまたは 1 つ以上の行を生成するパス式。
返される結果列の定義。これには、列名、結果データ・タイプ、および列情報を見つけるために使用するパス式が含まれます。

シンプルな情報を返す

JSON_TABLE を使用して、まず、EMP 表に保管されている JSON から従業員の名前とオフィス番号を取り出します。

SELECT t.first, t.last, t.office 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (             
                            first VARCHAR(10) PATH 'lax $.name.first',
                            last VARCHAR(10) PATH 'lax $.name.last',
                            office VARCHAR(10) PATH 'lax $.office'
                            )
                   ) AS t;

この例では、最初の引数で、処理する JSON のソースを指示しています。この場合は、EMP 表内の JSONDOC 列です。2 番目の引数は、JSON 文書内の開始点を指すパスです。$ は、先頭から開始することを指示しています。その次にあるのは、結果列の定義です。それぞれが、名前、データ・タイプ、および JSON オブジェクト内の列データを見つけるために使用するパスを保持しています。これらの各列に対して、現行コンテキスト ($) とその後に続く値のキー名を使用するように、列パスが指定されています。

この照会の結果は以下のようになります。

FIRST	LAST	OFFICE
John	Doe	E-334
Peter	Pan	E-216
Mary	Jones	E-739
Sally	Smith	(null)

Sally Smith に office が設定されていないことによる構造上のエラーが NULL 値として返されていることに注意してください。このような動作には、2 つの要素が影響しています。office 列のパスに lax が使用されたため、構造上のエラーが無視され、null が返されました。office パスに strict が使用されていた場合には、パス・ナビゲーションによってエラーが返されていたはずです。列に関してエラーが返された場合の JSON_TABLE のデフォルトの動作は、NULL 値を返すことです。strict モードであれば、列定義に ERROR ON ERROR 節を追加することで、これをオーバーライドできます。

JSON フォーマット設定されたデータを返す

JSON_TABLE には、JSON としてフォーマット設定されたデータを含んだ列を返す機能があります。これは、列定義にキーワード FORMAT JSON を使用することで実現されます。結果は、JSON オブジェクト、JSON 配列、またはスカラー値のいずれかの単一値で構成されなければなりません。

以下に、従業員の名前情報を JSON データとして取り出すために JSON_TABLE を使用する場合の例を示します。

SELECT t.id, t.name, t.office 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (             
                            id INTEGER PATH 'lax $.id',
                            name VARCHAR(100) FORMAT JSON PATH 'lax $.name',
                            office VARCHAR(10) FORMAT JSON PATH 'lax $.office'
                            )
                   ) AS t;

この照会は下記の結果を生成します。

ID	NAME	OFFICE
901	{"first":"John","last":"Doe"}	"E-334"
902	{"first":"Peter","last":"Pan"}	"E-216"
903	{"first":"Mary","last":"Jones"}	"E-739"
904	{"first":"Sally","last":"Smith"}	(null)

NAME 列には、JSON フォーマット設定されたオブジェクトを表すストリングが返されている点に注目してください。

FORMAT JSON 列のパスが結果としてストリング値になったときのデフォルトの動作は、ストリング値に引用符を付けて返すことです。これは、OFFICE 列の結果に示されています。OMIT QUOTES ON SCALAR STRING 節を使用すると、スカラー・ストリングから引用符を削除できます。

FORMAT JSON データを返す場合に適用される、ここでは例示されていないオプションがもう 1 つあります。パスによって JSON オブジェクトのシーケンスを見つける場合、それらのオブジェクトが JSON 値として正常に返されるためには、それらのオブジェクトが配列内にラップされている必要があります。これは、WITH ARRAY WRAPPER 節を使用して実行できます。デフォルトでは、ラッパーは追加されず、これは結果としてエラーになります。

JSON 配列の処理

JSON 配列から情報を返す場合、各配列エレメントは、結果表の別個の行として返されます。次は、JSON_TABLE を使用して、JSON 配列内にある電話番号のタイプと番号を取り出します。

SELECT t.type, t.number 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $.phones[*]'               
                   COLUMNS (             
                            type VARCHAR(20) PATH 'lax $.type',
                            number VARCHAR(20) PATH 'lax $.number'
                            )
                   ) AS t;

この例の場合、パス式は $.phones[*] であり、phones 配列のすべてのエレメントを意味します。JSON オブジェクト内の列データを見つけるために使用される列パス式は、コンテキスト項目 $ と、その後に続く、返すべき値のキー名です。このケースでは、コンテキスト項目は親パス式 $.phones[*] の結果です。

この照会の結果は以下のようになります。

TYPE	NUMBER
home	555-3762
work	555-7242
work	555-8925
work	555-4311
home	555-6312

ネストされた情報の処理

前の例の場合、電話番号のみを返す処理はあまり意味がありません。なぜなら、その電話番号に関連付けられた個人に関する情報が返されないからです。この情報を取得するために、ネストされた列を定義する必要があります。ネストされた列を使用すると、配列の値を、マルチレベル JSON オブジェクト内の上位レベルに存在するデータ項目と関連付けることが可能になります。

この例では、ネストされた列を使用して、電話番号に関連付けられた名前を返します。

SELECT t.* 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (
                            outer_ordinality FOR ORDINALITY,
                            first VARCHAR(10) PATH 'lax $.name.first',
                            last VARCHAR(10) PATH 'lax $.name.last',
                            NESTED PATH 'lax $.phones[*]'
                                 COLUMNS (             
                                          nested_ordinality FOR ORDINALITY,
                                          type VARCHAR(20) PATH 'lax $.type',
                                          number VARCHAR(20) PATH 'lax $.number'
                                          )
                            )
                   ) AS t;

行パス式は、$ であり、JSON オブジェクトのトップレベルを意味しています。最初の列と 2 番目の列で、名と姓を返します。この後に、ネストされた列定義が続きます。ネストされたパスのパス lax $.phone[*] は、配列 phones のすべてのエレメントを処理することを意味します。その配列内で、配列エレメントの type と number の値が、表の最後の 2 つの列として返されます。

この照会には、序数列の概念も例示されています。これは、JSON_TABLE の各呼び出しの結果行ごとに、1 から始まる数値を生成する列のことです。

この照会の結果は以下のようになります。

OUTER_ ORDINALITY	FIRST	LAST	NESTED_ ORDINALITY	TYPE	NUMBER
1	John	Doe	1	home	555-3762
1	John	Doe	2	work	555-7242
1	Peter	Pan	1	work	555-8925
1	Mary	Jones	1	work	555-4311
1	Mary	Jones	2	home	555-6312
1	Sally	Smith	1	(null)	(null)

この例では、ネスト・レベルのレベル間に親/子の関係があります。情報を結合して親/子関係にするために、LEFT OUTER JOIN が使用されています。Sally Smith には電話の情報がないため、LEFT OUTER JOIN は phone 列に NULL 値を返します。

ここで、2 つある序数列について見てみましょう。親レベルでは、すべての行が同じ序数値を持ちます。各行に連番が割り振られることを予想したかもしれませんが、この照会は、JSONDOC の行ごとに別個の JSON_TABLE の呼び出しを実行します。呼び出しごとに、番号付けは 1 から始まります。したがって、結果内の行はすべて OUTER_ORDINALITY に 1 を持つことになります。この例で使用した JSON が、4 人の従業員全員を含んだ 1 つのオブジェクトであったならば、OUTER_ORDINALITY は、従業員オブジェクトごとに増やされていました。NESTED_ORDINALITY の番号付けは、親が変更されるたびに 1 から再開されます。

兄弟のネスティング

ネストされた列同士は、同一レベルで存在できます。以下の例は、ネストされた兄弟の列を使用して、電話情報とアカウント情報の両方を返します。最初のネストされた列の節は電話情報にアクセスします。2 番目のネストされた列の節は、アカウント情報にアクセスします。

SELECT t.first, t.last, t.type, t.number, t.account 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (
                            first VARCHAR(10) PATH 'lax $.name.first',
                            last VARCHAR(10) PATH 'lax $.name.last',
                            NESTED PATH 'lax $.phones[*]'
                                 COLUMNS (             
                                          type VARCHAR(20) PATH 'lax $.type',
                                          number VARCHAR(20) PATH 'lax $.number'
                                          ),
                            NESTED PATH 'lax $.accounts[*]'
                                 COLUMNS (             
                                          account VARCHAR(20) PATH 'lax $.number'
                                          )
                            )
                   ) AS t;

この照会の結果は以下のようになります。

FIRST	LAST	TYPE	NUMBER	ACCOUNT
John	Doe	home	555-3762	(null)
John	Doe	work	555-7242	(null)
John	Doe	(null)	(null)	36232
John	Doe	(null)	(null)	73263
Peter	Pan	work	555-8925	(null)
Peter	Pan	(null)	(null)	76232
Peter	Pan	(null)	(null)	72963
Mary	Jones	work	555-4311	(null)
Mary	Jones	home	555-6312	(null)
Mary	Jones	(null)	(null)	(null)
Sally	Smith	(null)	(null)	(null)
Sally	Smith	(null)	(null)	(null)

この例では、電話情報とアカウント情報の間に兄弟関係があります。兄弟関連のネストでは、UNION を使用して情報を結合します。電話情報とアカウント情報は、JSON 文書内で同一レベルに位置しているため、それら両方の情報を含んでいる結果行は存在しません。