2

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!

peegee
  • 41
  • 1
  • 6
  • Does it work if one row has 2 items and the other has 3? – ypercubeᵀᴹ May 06 '22 at 17:46
  • I think it would likely work with the similar from table_abc as abc, abc.items_list as unnested_list_items – ypercubeᵀᴹ May 06 '22 at 17:50
  • @ypercubeᵀᴹ yes it works with different lengths of list.

    hmm, tried your syntax and it didn't seem to process. The query engine doesn't seem to be cross joining as the items only appear column_a values they are associated with. It seems that the left join of the column is somehow an implicit unnest function. Very puzzled by this.

     – peegee May 06 '22 at 18:04
  • Yes, it treats the list (when it appears in the FROM clause as a table so it unnests it. – ypercubeᵀᴹ May 06 '22 at 18:16
  • Hm, I don't know. My guess is that they (Redshift) decided to do an implicit unnest - what in Postgres would require an implicit from table_abc as abc, unnest(abc.items_list) as unnested_list_items or from table_abc as abc left join unnest(abc.items_list) as unnested_list_items on true – ypercubeᵀᴹ May 06 '22 at 18:25
  • They kind of explain it in their docs: Querying semistructured data (go to "Unnesting queries", they have an example) – ypercubeᵀᴹ May 06 '22 at 18:27
  • @ypercubeᵀᴹ yeah that appears to be it. Thanks! I was sure it must be somewhere in the docs, but I was trying different search terms and was having no luck finding it.

    Btw, I tried the comma separated from syntax like in your second comment again and it does work. The issue I had before was that I wasn't explicitly referencing the table abc in the select statement when selecting columns that weren't the items list. So column_a needs to be referenced like abc.column_a

     – peegee May 09 '22 at 09:49
  • Feel free to post an answer with the info discussed in our comments – ypercubeᵀᴹ May 09 '22 at 13:45

1 Answers1

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
peegee
  • 41
  • 1
  • 6