順序変換式

構文

transform_expression ::= SEQ_TRANSFORM "(" expression "," expression ")"

セマンティクス

順序変換式は、順序を別の順序に変換します。構文的には、seq_transformという名前の関数のように見えます。最初の引数は、変換する順序(入力順序)を生成する式であり、2番目の引数は入力順序の各項目に対して計算されるマッパー式です。seq_transform式の結果は、マッパー式の各評価によって生成される順序の連結となります。マッパー式は、$変数を介して現在の入力項目にアクセスできます。

例6-57 順序変換式

たとえば、次のデータを持つsales表があるとします。

CREATE TABLE sales (
    id INTEGER,
    sale RECORD (
        acctno INTEGER,
        year INTEGER,
        month INTEGER,
        day INTEGER,
        state STRING,
        city STRING,
        storeid INTEGER,
        prodcat STRING,
        items ARRAY(
            RECORD (
                prod STRING,
                qty INTEGER,
                price INTEGER
            )
        )
    ),
    PRIMARY KEY (id)
);

INSERT INTO sales VALUES (
    1,
    {
        "acctno" : 349,
        "year" : 2000, 
        "month" : 10, 
        "day" : 23,
        "state" : "CA", 
        "city" : "San Jose", 
        "storeid" : 76,
        "prodcat" : "vegies",
        "items" :[ 
            { "prod" : "tomatoes", "qty" : 3, "price" : 10.0 },
            { "prod" : "carrots", "qty" : 1, "price" : 5.0 },
            { "prod" : "pepers", "qty" : 1, "price" : 15.0 }
        ]
    }
);

salesに次の索引があるとします。

CREATE INDEX idv1 ON sales (
    sale.acctno, sale.year, sale.prodcat);

次に、アカウント番号と年度ごとの総売上を返す次の問合せを記述できます。

SELECT t.sale.acctno,
t.sale.year,
sum(seq_transform(t.sale.items[], $.price * $.qty)) AS sales
FROM sales t
GROUP BY t.sale.acctno, t.sale.year;

JSONドキュメントの順序変換式の使用:

表の行に格納されたJSONドキュメントを変換するために、順序変換式を使用できます。このような場合、相互にネストされている複数の順序変換式を使用することがよくあります。ここで、内部の順序変換マッパー式は、外部の順序変換の現在の項目にアクセスすることが必要な場合があります。これを可能にするために、各順序変換式'S'は$sqNという名前の変数を宣言します。ここで、Nは、外部順序変換式内の式'S'のネストのレベルです。$sqNは基本的に$のシノニムであり、入力式'S'によって返される項目にバインドされます。ただし、$sqNは、式'S'内でネストできる他の順序変換式によりアクセスできます。例を見てみます。

baggageInfo表には、航空会社の乗客の荷物の処理に関する情報が格納されます。

CREATE TABLE baggageInfo (
  ticketNo string,
  passengerName string,
  bagInfo json,
  primary key(ticketNo)
)

この表のサンプル行を次に示します。

{
  "ticketNo" : "1762352483606",
  "passengerName" : "Willie Hernandez",
  "bagInfo" : [
    {
      "tagNum" : "17657806243915",
      "routing" : "SFO/AMS/HER",
      "lastActionCode" : "offload",
      "lastSeenStation" : "HER",
      "lastSeenTimeGmt" : "2019-03-13T15:19:00",
      "flightLegs" : [
        {
          "flightNo" : "BM604",
          "flightDate" : "2019-03-12T20:00:00",
          "fltRouteSrc" : "SFO",
          "fltRouteDest" : "AMS",
          "estimatedArrival" : "2019-03-13T08:00:00",
          "actions" : [
            { “at”:”SFO”, "action":"TagScan", "time":"2019-03-12T18:14:00" },
            { “at”:”SFO”, "action":"onload",  "time":"2019-03-12T19:20:00" },
            { “at”:"AMS", “action”:"offload", "time":"2019-03-13T08:30:00" }
          ]
        },
        {
          "flightNo" : "BM667",
          "flightDate" : "2019-03-13T11:14:00",
          "fltRouteSrc" : "AMS",
          "fltRouteDest" : "HER",
          "estimatedArrival" : "2019-03-13T15:00:00",
          "actions" : [
            { “at”:”AMS”, "action":"TagScan", "time":"2019-03-13T10:45:00" },
            { “at”:”AMS”, "action":"onload",  "time":"2019-03-13T10:50:00" },
            { “at”:”HER”, "action":"offload", "time":"2019-03-13T15:19:00" }
          ]
        }
      ]
    },
    {
      "tagNum" : "17657806244523",
      "routing" : "SFO/AMS/HER",
      "lastActionCode" : "offload",
      "lastSeenStation" : "AMS",
      "lastSeenTimeGmt" : "2019-03-13T08:35:00",
      "flightLegs" : [
        {
          "flightNo" : "BM604",
          "flightDate" : "2019-03-12T20:00:00",
          "fltRouteSrc" : "SFO",
          "fltRouteDest" : "AMS",
          "estimatedArrival" : "2019-03-13T08:00:00",
          "actions" : [
            { “at”:”SFO”, "action":"TagScan", "time":"2019-03-12T18:14:00" },
            { “at”:”SFO”, "action":"onload",  "time":"2019-03-12T19:22:00" },
            { “at”:”AMS”, "action":"offload", "time":"2019-03-13T08:32:00"  }
          ]
        }
      ]
    }
  ]
}

問合せ: ticketNoごとに、そのticketNoの荷物に対して実行されたすべてのアクションを含むフラット配列をフェッチします。つまり、各アクションの"at"および"action"フィールドをフェッチします。また、flightNoおよびtagNumを各アクションとともに表示します。問合せの結果は次のとおりです。

{
  “actions” : [
    {“at”:”SFO”, “action”:”TagScan”, “flightNo”:”BM604”, “tagNum”:17657806243915},
    {“at”:”SFO”, “action”:”onload”,  “flightNo”:”BM604”, “tagNum”:17657806243915},
    {“at”:”AMS”, “action”:”offload”, “flightNo”:”BM604”, “tagNum”:17657806243915},
    {“at”:”AMS”, “action”:”TagScan”, “flightNo”:”BM667”, “tagNum”:17657806243915},
    {“at”:”AMS”, “action”:”onload”,  “flightNo”:”BM667”, “tagNum”:17657806243915},
    {“at”:”HER”, “action”:”offload”, “flightNo”:”BM667”, “tagNum”:17657806243915},
    {“at”:”SFO”, “action”:”TagScan”, “flightNo”:”BM604”, “tagNum”:17657806244523},
    {“at”:”SFO”, “action”:”onload”,  “flightNo”:”BM604”, “tagNum”:17657806244523},
    {“at”:”AMS”, “action”:”offload”, “flightNo”:”BM604”, “tagNum”:17657806244523},
  ]
}

順序変換式を使用して、前述の出力を取得できます。

SELECT
  seq_transform(
      l.bagInfo[],
      seq_transform(
          $sq1.flightLegs[],
          seq_transform(
              $sq2.actions[],
              {
                "at" : $sq3.at,
                “action” : $sq3.action,
                "flightNo" : $sq2.flightNo,
                "tagNum" : $sq1.tagNum
              }
          )
      )
  ) AS actions
FROM baggageInfo l