What does `t(sites)` means for presto query?

Question

Here is snippet from aws site:

WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    ),
    CAST(
      ROW('news.cnn.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    ),
    CAST(
      ROW('netflix.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    )
  ] as items
)
SELECT sites.hostname, sites.flaggedActivity.isNew
FROM dataset, UNNEST(items) t(sites)
WHERE sites.flaggedActivity.isNew = true;

And it works! But what t(sites) means? When I try to use real table instead dataset of I got error Table 'site' not found.

It looks very strange - like function which is invoked with UNNEST keyword. Can somebody explain what is this?

score 6 · Accepted Answer · answered Jun 25 '20 at 16:50

FROM dataset, UNNEST(items) t(sites)

UNNEST() is a set returning-function: it produces a series of rows, with one column holding the values from the original array.

This set of rows is also called a derived table. Here t is an alias for the derived table, and site is the name of the (only) column in this derived table.

It might be easier to understand if we add the AS keyword:

FROM dataset, UNNEST(items) AS t(sites)

What does `t(sites)` means for presto query?

1 Answers1

Linked