Sequence Transform Expressions

Syntax

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

Semantics

A sequence transform expression transforms a sequence to another sequence. Syntactically it looks like a function whose name is seq_transform. The first argument is an expression that generates the sequence to be transformed (the input sequence) and the second argument is a "mapper" expression that is computed for each item of the input sequence. The result of the seq_transform expression is the concatenation of sequences produced by each evaluation of the mapper expression. The mapper expression can access the current input item via the $ variable.

Example 6-57 Sequence Transform Expression

As an example, assume a "sales" table with the following data.

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 }
        ]
    }
);

Assume there is the following index on sales:

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

Then we can write the following query, which returns the total sales per account number and year:

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;

Using sequence transform expression for JSON documents :

You can use the sequence transform expression for transforming JSON documents stored in table rows. In such cases you often use multiple sequence transform expressions nested inside each other. Here the mapper expression of an inner sequence transform may need to access the current item of an outer sequence transform. To allow this, each sequence transform expression 'S' declares a variable with name $sqN, where N is the level of nesting of the expression 'S' within the outer sequence transform expressions. $sqN is basically a synonym for $, that is, it is bound to the items returned by the input expression 'S'. However, $sqN can be accessed by other sequence transform expressions that may be nested inside the expression 'S'. Let’s illustrate with an example.

The baggageInfo table stores information about handling the luggage of passengers in an airline.

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

A sample row for this table is shown below.

{
  "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"  }
          ]
        }
      ]
    }
  ]
}

Query: For each ticketNo, fetch a flat array containing all the actions performed on the luggage of that ticketNo. That means fetch the “at” and “action” fields of each action. Also display the flightNo and the tagNum with each action. The result of the query is the following:

{
  “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},
  ]
}

You could use sequence transform expression to get the above output.

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