Redshift Spectrum Implicit Unnest of List in Join

Question

I have data that is stored in S3 and I am querying it using SQL via Redshift Spectrum. One of the columns is stored as a list in the S3 file like so:

Table_ABC

column_a	items_list
a.	[item_1, item_2]
b.	[item_3, item_4]

Redshift has no unnest function, but I have picked out of our codebase the syntax below that works to unnest the list.

select 
    column_a,
    unnested_list_items
from table_abc as abc
left join abc.items_list as unnested_list_items on true

Running this results in:

column_a	unnested_list_items
a.	item_1.
a.	item_2.
b.	item_3.
b.	item_4.

No one seems to understand why this works and I can't find any documentation on this and why it works. The closest I can find is the documentation below on Athena syntax, but the unnest seems to be implicit and afaik it's not being processed by Athena.

https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html

Is anyone able to explain how this syntax is processed by the query engine or point to any documentation on this?

I am using Redshift 1.0.37680. Thanks!

Answer 1

As found in the documentation here under 'Querying semistructured data' the from clause syntax x as y means that y is iterating over x.

In the syntax in the example abc.items_list as unnested_items_list means that unnested_items_list is iterating over the array abc.items_list.

*** Update and edit ***

The docs above refer to handling of super types in Redshift. The example posted above was being accessed in a datalake through Redshift Spectrum. There are some slight differences in the way that data can be accessed and manipulated via Spectrum. For instance the index of a simple array is not able to be accessed via Spectrum.

The document linked here is Spectrum specific and also provides more detail as to how complex data types are processed in Spectrum when referenced in the from clause of the query. It also explains further the behaviour of different types of joins.

Relevant to the example above the docs explain the following.

For a table with the structure:

CREATE EXTERNAL TABLE spectrum.customers (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)

For the code:

SELECT c.id, o.shipdate
FROM   spectrum.customers c, c.orders o 

"You can think of the FROM clause as running the following nested loop, which is followed by SELECT choosing the fields to output."

for each customer c in spectrum.customers
  for each order o in c.orders
     output c.id and o.shipdate