WITH & UNNEST in BigQuery

user_3064736 0
Viewed 3544

I find it helpful to use WITH blocks to break up logic in a query. Am I correct in thinking I can't do this on a nested field within an ARRAY expression? I can separate into nested queries - which is sufficient but not ideal.

For example, this works:

SELECT
  repository.url,
  ARRAY(
  SELECT
    action
  FROM (
    SELECT
      action
    FROM
      UNNEST(payload.pages))) action
FROM
  `bigquery-public-data.samples.github_nested`

...but this doesn't:

SELECT
  repository.url,
  ARRAY(
  WITH
    p AS (
    SELECT
      action
    FROM
      UNNEST(payload.pages))
  SELECT
    action
  FROM
    p) action
FROM
  `bigquery-public-data.samples.github_nested`

...Error: Unrecognized name: payload at [9:14]. Potentially because the table hasn't yet been defined for that WITH block?

Is there another way to do this?

EDIT: This is toy query to illustrate the issue, not representative of the actual queries we're running

google-bigquery
1 Answers

The only option I see is just to simplify/optimize your query to below - so there will be no need for WITH inside ARRAY 

#standardSQL
SELECT
  repository.url,
  ARRAY(SELECT action FROM UNNEST(payload.pages)) action
FROM `bigquery-public-data.samples.github_nested`
user_52219440
Related