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-99 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'.

You can illustrate this with the following example:

Example 6-100 For each ticketNo, fetch a flat array containing all the actions performed on the luggage of that ticketNo from the airline baggage tracking application.

In this query, you fetch the at and action fields for each action. Also display the flightNo and the tagNum with each action. You can use the sequence transform expression to get a flat array of actions.

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

Output:

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