1. SQLリファレンス・ガイド
  2. SQL問合せ管理
  3. 配列およびマップのネスト解除

配列およびマップのネスト解除

unnest_syntaxを使用して、配列またはマップを含む行をフラット化します。

ネスト解除の構文

構文的には、ネスト解除は、SELECT文のFROM句で(関連する変数を含む)式のリストとして指定されます。これらの式の一部またはすべてをUNNEST句に含めることができます。
unnest_expression::=expression AS VARNAME
unnest_clause ::= UNNEST((unnest_expression)*)
unnest_syntax ::=(unnest_expression | unnest_clause)*

セマンティクス

SELECT文のFROM句には、式のリスト(関連する変数を含む)を含めることができます。通常、配列またはマップをネスト解除する式を使用し、FROM句では、ネスト解除された配列/マップの値から新しい行のセットを作成します。これらの式の一部またはすべてをUNNEST句に含めることができます。意味的に、ネスト解除演算子はno-opであり、つまり、UNNEST句が使用されているかどうかは、FROM句の結果を変更せず、unnest(expr)はexprと同じです。UNNEST句の目的は、最適化ヒントとして機能することです。具体的には、ネスト解除される配列/マップに索引がある場合、問合せでUNNEST句が使用されている場合のみ、索引を問合せで使用できます。また、このような索引を使用する問合せプロセッサを支援するために、UNNEST句では、その中に現れる式にいくつかの制限があります。

通常、配列/マップ内のフィールドでグループ化する場合は、ネスト解除を使用します。ただし、次の2つの例は、ネスト解除式のセマンティクスを示しています。このため、次の問合せにGROUP BY句は使用されません。

FROM句での単一のネスト解除式の使用(単一のマップのネスト解除):

2つの列を持つ表を作成します。1つはデータ型INTEGERで主キー列であり、2番目はJSONデータ型です。

ノート:

例で使用されているJSON列はマップです。 
create table sample_unnest ( samp_id INTEGER, samp_data JSON, PRIMARY KEY(samp_id))
INSERT INTO sample_unnest VALUES(1,
{ "episodeID" : 20, "lengthMin" : 40, "minWatched" : 40 })
INSERT INTO sample_unnest VALUES(2,
{ "episodeID" : 25, "lengthMin" : 20, "minWatched" : 18 })
select * from sample_unnest
        $s1,unnest($s1.samp_data.values() as $s2) where $s1.samp_id=1

前述のFROM句は、変数$s1により表sample_unnestおよび変数$s2によりマップsamp_dataの値を参照します。概念的には、FROM句の結果は2列の表temp_tblです。列1には表sample_unnestのすべての行が格納され、列2にはsamp_dataマップの要素のすべての値が格納されます。

表6-4 temp_tbl表内のレコード

$s1 $s2
"samp_id":1,"samp_data":{"episodeID":20,"lengthMin":40,"minWatched":40} 20
"samp_id":1,"samp_data":{"episodeID":20,"lengthMin":40,"minWatched":40} 40
"samp_id":1,"samp_data":{"episodeID":20,"lengthMin":40,"minWatched":40} 40
"samp_id":2,"samp_data":{"episodeID":25,"lengthMin":20,"minWatched":18} 25
"samp_id":2,"samp_data":{"episodeID":25,"lengthMin":20,"minWatched":18} 20
"samp_id":2,"samp_data":{"episodeID":25,"lengthMin":20,"minWatched":18} 18
他の問合せがどのように実行されているかを確認するには、上の表からすべての行を取得して、WHERE条件を適用します。条件がTRUE ($s1.samp_id=1)になる行については、対応する行が問合せ結果に含まれます。条件は最初の3行についてTRUE、最後の3行ではFALSEと評価されます。

表6-5 問合せ出力

$s1 $s2
{"samp_id":1, "samp_data":{"episodeID":20,"lengthMin":40,"minWatched":40}} 20
{"samp_id":1, "samp_data":{"episodeID":20,"lengthMin":40,"minWatched":40}} 40
{"samp_id":1, "samp_data":{"episodeID":20,"lengthMin":40,"minWatched":40}} 40

FROM句での2つのネスト解除式の使用(配列および配列の下にネストされているマップをネスト解除するため)

UNNEST句では、複数のパス式を使用できます。"M"をfrom式の数とすると、FROM句の結果表がMステップで計算され、それぞれ中間表が生成されます。

ノート:

例で使用されているJSON列はマップの配列です。 
create table sample_unnest (
        samp_id INTEGER, samp_data JSON, PRIMARY KEY(samp_id))

INSERT INTO sample_unnest VALUES(1,
{
   "episodes":[
     {
       "episodeID" : 10,
       "lengthMin" : 40,
       "minWatched" : 25
     },
     {
        "episodeID" : 20,
        "lengthMin" : 35,
        "minWatched" : 30
      }
    ]
  }
)

INSERT INTO sample_unnest VALUES(2,
{
   "episodes":[
    {
      "episodeID" : 30,
      "lengthMin" : 40,
      "minWatched" : 25
   },
   {
       "episodeID" : 40,
       "lengthMin" : 35,
       "minWatched" : 30
    }
   ]
  }
)

INSERT INTO sample_unnest VALUES(3,
{
   "episodes":[
     {
       "episodeID" : 10,
       "lengthMin" : 40,
       "minWatched" : 25
     },
     {
       "episodeID" : 20,
       "lengthMin" : 35,
       "minWatched" : 30
     }
   ]
 }
)
UNNEST句に2つのパス式を使用して問い合せます。
SELECT n.samp_id as customer,
        $epi.episodeID,$epi.minWatched AS length,$epiVal AS episode_details FROM  sample_unnest n,
        unnest(n.samp_data.episodes[] AS $epi, $epi.values()  AS $epiVal)
ステップ1 : FROM句(sample_unnest n, unnest(n.samp_data.episodes[] AS $epi)の最初の2つの式の結果である、最初の表temp_tbl1のレコード

表6-6 temp_tbl1内のレコード

n $epi
"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25},
"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":20,"lengthMin":35,"minWatched":30}
"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]} {"episodeID":30,"lengthMin":40,"minWatched":25},
"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]} {"episodeID":40,"lengthMin":35,"minWatched":30}
"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25},
"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":20,"lengthMin":35,"minWatched":30}
ステップ2 : 2番目の表temp_tbl2のレコード - 完全なFROM句の結果(前述の表は、UNNEST句内の2番目のパス式の結果と結合されています($epi.values()as $epiVal)

表6-7 temp_tbl2内のレコード

n $epi $epival
{"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25}, 10
{"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25} 40
{"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25} 25
{"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]}} {"episodeID":20,"lengthMin":35,"minWatched":30} 20
{"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]}} {"episodeID":20,"lengthMin":35,"minWatched":30} 35
{"s1":{"samp_id":1,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]}} {"episodeID":20,"lengthMin":35,"minWatched":30} 30
{"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]}} {"episodeID":30,"lengthMin":40,"minWatched":25} 30
{"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]}} {"episodeID":30,"lengthMin":40,"minWatched":25} 40
{"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]}} {"episodeID":30,"lengthMin":40,"minWatched":25} 25
{"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]}} {"episodeID":40,"lengthMin":35,"minWatched":30} 40
{"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]}} {"episodeID":40,"lengthMin":35,"minWatched":30} 35
{"s1":{"samp_id":2,"samp_data":{"episodes":[{"episodeID":30,"lengthMin":40,"minWatched":25},{"episodeID":40,"lengthMin":35,"minWatched":30}]}} {"episodeID":40,"lengthMin":35,"minWatched":30} 30
{"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25} 10
{"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25} 40
{"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":10,"lengthMin":40,"minWatched":25} 25
{"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":20,"lengthMin":35,"minWatched":30} 20
{"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":20,"lengthMin":35,"minWatched":30} 35
{"s1":{"samp_id":3,"samp_data":{"episodes":[{"episodeID":10,"lengthMin":40,"minWatched":25},{"episodeID":20,"lengthMin":35,"minWatched":30}]} {"episodeID":20,"lengthMin":35,"minWatched":30} 30
ステップ3 : 問合せにフィルタがなく、SELECT句のフィールドがフェッチされます。問合せの最終結果は次のとおりです。

表6-8 問合せ結果

顧客 episode_id 長さ episode_details
2 30 25 30
2 30 25 40
2 30 25 25
2 40 30 40
2 40 30 35
2 40 30 30
1 10 25 10
1 10 25 40
1 10 25 25
1 20 30 20
1 20 30 35
1 20 30 30
3 10 25 10
3 10 25 40
3 10 25 25
3 20 30 20
3 20 30 35
3 20 30 30

UNNEST句での式の使用の制限

UNNEST句で使用される式にはいくつかの制限があります。
  • UNNEST句で使用するすべての式は、パス式である必要があります。
  • パス式内の配列フィルタまたはマップ・フィルタ・ステップでは、述語は許可されません。
  • 式は変数で開始する必要があります。式リストの最初の式である場合、その式はターゲット表に関連付けられた表の別名である必要があります。
  • ネストされた配列をネスト解除すると、各パス式は1レベル深くネスト解除し、前のレベルのネスト解除によって生成された値に対して動作します。これらの値は、前のパス式に関連付けられた変数で表されます。そのため、各式の開始変数は、前の式に関連付けられている変数である必要があります。これは、UNNEST句の式のリストの最初の式である場合は適用されません。
  • 式は[]または.values()ステップで終了する必要があります。
  • UNNEST句内で定義された変数は、FROM句の残りの部分で参照できません。それらは、UNNEST句内およびFROM句外で参照できます。
パス式の詳細およびこれらの式が配列またはマップに対してどのように評価されるかについては、パス式を参照してください。