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-49 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;