Nested Fields in Google BigQuery

Question

I'm trying to un-nest some nested fields and do a sub-query in Google BigQuery.

This works fine.

SELECT id, 
custom_field_options
FROM `project.database.ticket_fields`

'custom_field_options' is a nested field, and that's causing some problems for me. I have 'custom_field_options.id' and 'custom_field_options.name' and a couple other nested fields.

This also works fine.

SELECT ticket_fields.id,
ticket_fields.raw_title,
ticket_custom_fields.name AS ticket_custom_fields_name,
ticket_custom_fields.raw_name AS ticket_custom_fields_raw_name,
ticket_custom_fields.value AS ticket_custom_fields_value,
ticket_fields.created_at
FROM `project.database.ticket_fields` AS ticket_fields
LEFT JOIN UNNEST(custom_field_options) AS ticket_custom_fields
GROUP BY 1,2,3,4,5,6

When run separately, both those queries run fine. Now, I'm trying to run the first query as a sub-query of the second, to pull back all ids from that query, and based on that, run the query below.

SELECT ticket_fields.id,
ticket_fields.raw_title,
ticket_custom_fields.name AS ticket_custom_fields_name,
ticket_custom_fields.raw_name AS ticket_custom_fields_raw_name,
ticket_custom_fields.value AS ticket_custom_fields_value,
ticket_fields.created_at
FROM `project.database.ticket_fields` AS ticket_fields
LEFT JOIN UNNEST(custom_field_options) AS ticket_custom_fields
  WHERE id IN (SELECT id, 
  custom_field_options
  FROM `project.database.ticket_fields`)
GROUP BY 1,2,3,4,5,6

Elliott Brossard Elliott Brossard · Accepted Answer · 2018-10-16T22:25:07

You can qualify the id column name to make the reference unambiguous:

SELECT ticket_fields.id,
ticket_fields.raw_title,
ticket_custom_fields.name AS ticket_custom_fields_name,
ticket_custom_fields.raw_name AS ticket_custom_fields_raw_name,
ticket_custom_fields.value AS ticket_custom_fields_value,
ticket_fields.created_at
FROM `project.database.ticket_fields` AS ticket_fields
LEFT JOIN UNNEST(custom_field_options) AS ticket_custom_fields
  WHERE ticket_fields.id IN (SELECT id, 
  custom_field_options
  FROM `project.database.ticket_fields`)
GROUP BY 1,2,3,4,5,6

(I'm assuming that ticket_fields.id is the id that you want to search for)

Nested Fields in Google BigQuery

1 Answers