Sequence Transform Expressions

A sequence transform expression transforms a sequence into 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: For each ticketNo, fetch a flat array containing all the actions performed on the luggage of that ticketNo.

SELECT seq_transform(l.bagInfo[],
      seq_transform(
          $sq1.flightLegs[],
          seq_transform(
              $sq2.actions[],
              {
                "at" : $sq3.actionAt,
                "action" : $sq3.actionCode,
                "flightNo" : $sq2.flightNo,
                "tagNum" : $sq1.tagNum
              }
          )
      )
  ) AS actions
FROM baggageInfo l WHERE ticketNo=1762340683564

Explanation: 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.

Output:

{ 
 "actions":[ 
   {"action":"ONLOAD to HKG","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
   {"action":"BagTag Scan at YYZ","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
   {"action":"Checkin at YYZ","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
   {"action":"Offload to Carousel at BLR","at":"BLR","flightNo":"BM115","tagNum":"17657806299833"},
   {"action":"ONLOAD to BLR","at":"HKG","flightNo":"BM115","tagNum":"17657806299833"},
   {"action":"OFFLOAD from HKG","at":"HKG","flightNo":"BM115","tagNum":"17657806299833"} 
 ]
}