UP10100 Cannot Select Records in an UNNEST Statement

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

Possible Causes

Example:

CREATE SYNC JOB extract_emails
    ADD_MISSING_COLUMNS = TRUE
AS INSERT INTO default_glue_catalog.example.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 $event_time BETWEEN RUN_START_TIME() AND RUN_END_TIME());

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.example.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 $event_time BETWEEN RUN_START_TIME() AND RUN_END_TIME());

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.