UP10100 - Can not Select Records in an UNNEST Statement

Possible Causes

• You tried to select an expression of type row/record within an UNNEST SELECT statement which is not supported. For example:

CREATE SYNC JOB extract_emails
    ADD_MISSING_COLUMNS = TRUE
AS INSERT INTO default_glue_catalog.exmaple.tbl MAP_COLUMNS_BY_NAME
UNNEST(SELECT  
    orderid::STRING as orderid,
    $event_date::DATE as partition_date,
    MAP_WITH_INDEX(emails) AS emails
FROM default_glue_catalog.example.raw_data_table
WHERE time_filter());

Here, MAP_WITH_INDEX returns a row type with multiple subfields, this can't appear in the SELECT CLAUSE of an UNNEST query.

Possible Solutions

Use a LET statement and select specific fields of the resulted record, for example:

CREATE SYNC JOB my_merge_job
    ADD_MISSING_COLUMNS = TRUE
AS INSERT INTO default_glue_catalog.exmaple.tbl MAP_COLUMNS_BY_NAME
UNNEST(SELECT  
    orderid::STRING as orderid,
    $event_date::DATE as partition_date,
    email_records[].index AS emails,
    email_records[].value AS emails
FROM default_glue_catalog.example.raw_data_table
LET email_records = MAP_WITH_INDEX(emails)
WHERE time_filter());

By incorporating the LET statement and making use of the subfields fields (email_records[].index and email_records[].value), you can appropriately handle the record-type expression within the UNNEST SELECT statement.