Enhancement: GCP BigQuery UNNEST syntax

[Jerodsun]

Hi,

Thank you for supporting this repo. GCP BigQuery has nested arrays that are flattened via the UNNEST operator. Time pending, I'd like to explore a method to add functionality to process UNNEST in queries. For example, given

SELECT A, B, metrics.C, metrics.D
FROM table,
UNNEST(metrics) as metrics

the parser for this query includes UNNEST and metrics as table names. A simple solution for my use case would be to simply remove the UNNEST group with regex. There are definitely better ways to integrate this that I can test first.

[river2022next]

HI, what's going on?

[akburner]

Additionally, UNNEST WITH OFFSET fails to parse:

Example:

sql = f"""
SELECT A, B, metrics.C, metrics.D
FROM table,
UNNEST(metrics) with offset as metrics
"""

p = Parser(sql)

p.tables

Error:

with_names
    token.next_token_not_comment.normalized
AttributeError: 'NoneType' object has no attribute 'normalized'

The workaround would be something like this:

sql = f"""
WITH UnnestedMetrics AS (
    SELECT A, B, metrics, ARRAY_POSITION(metrics, metrics.C) AS offset
    FROM table,
    UNNEST(metrics) as metrics
)
SELECT A, B, metrics.C, metrics.D
FROM UnnestedMetrics
"""

p = Parser(sql)
p.tables

Result:

['table', 'UNNEST', 'metrics']