FROM UNPIVOT

FROM UNPIVOT s AS v AT k iterates over the fields of a struct: it binds v to each field’s value and k to its name. It turns a struct whose field names are data — ticker symbols, feature flag names, metric names — into rows that the rest of the query can filter, sort and aggregate. PIVOT does the opposite.

For the formal semantics, see "Ranging over Attribute-Value Pairs" in the FROM chapter of the PartiQL specification.

Iterating over a struct’s fields

Input data

{
  'closing_prices': { 'AMZN': 186.40, 'MSFT': 415.10, 'NVDA': 121.75 }
}

Query

SELECT symbol, price
FROM UNPIVOT closing_prices AS price AT symbol
ORDER BY price DESC

Result

[
  { 'symbol': 'MSFT', 'price': 415.10 },
  { 'symbol': 'AMZN', 'price': 186.40 },
  { 'symbol': 'NVDA', 'price': 121.75 }
]

Because of the ORDER BY, the result is an array ([ … ]) rather than a bag; see ORDER BY.

Field names can be filtered like any other value:

Input data

{
  'flags': { 'dark_mode': true, 'new_checkout': false, 'beta_search': true }
}

Query

SELECT VALUE name
FROM UNPIVOT flags AS enabled AT name
WHERE enabled = true

Result

<<
  'beta_search',
  'dark_mode'
>>

AT is optional. Without it, FROM UNPIVOT binds only the values.

Unpivoting a struct inside each row

Like any FROM item, UNPIVOT can range over a struct inside an earlier item’s element, so each row is paired with each of its own fields:

Input data

{
  'sensors': <<
    { 'room': 'Lab A', 'readings': { 'temp_c': 22.5, 'co2_ppm': 980 } },
    { 'room': 'Lab B', 'readings': { 'temp_c': 27.0, 'co2_ppm': 1430 } }
  >>
}

Query

SELECT s.room, metric, v AS reading
FROM sensors AS s, UNPIVOT s.readings AS v AT metric
WHERE v > 100

Result

<<
  { 'room': 'Lab A', 'metric': 'co2_ppm', 'reading': 980 },
  { 'room': 'Lab B', 'metric': 'co2_ppm', 'reading': 1430 }
>>

When the value isn’t a struct

What FROM UNPIVOT does with a value that isn’t a struct depends on the mode the query runs in. The specification defines both:

In permissive mode, a value that isn’t a struct is treated as the struct { '_1': value }, and MISSING as the empty struct {}.
In strict mode (also called type-checking mode), it is an error and the query fails.

Input data

{
  'theme': 'dark'
}

Query

SELECT name, v
FROM UNPIVOT theme AS v AT name

Result

<<
  { 'name': '_1', 'v': 'dark' }
>>

A MAP value isn’t a struct either, but it isn’t treated as one value: FROM UNPIVOT iterates over its entries, binding AT to each entry’s key, which need not be a string. See the MAP type RFC.