how to access values from type ARRAY>STRUCT from within a subquery in Google BigQuery

Question

I have a Google Big Query dataset with fields that are inside an array within a table. I can access values in the arrays using a subquery in the SELECT statemenmt. For example: (select custom_fields.value.value from tickets.custom_fields where custom_fields.value.id=35374507) AS Organization

The issue is that I need to access values in one array in, say, table A by matching it to values in another array in table B. I first tried to do this by using a subquery within a subquery in the SELECT statement as in the following:

tickets.id,
(select (select custom_field_options.value.name from ticket_fields.custom_field_options
    where custom_field_options.value.value=(select custom_fields.value.value from 
    tickets.custom_fields where custom_fields.value.id=360014264753))
    from zendesk.ticket_fields) AS Issue
from zendesk.tickets
where tickets.id=6869

But that gave the following error: "Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN."

I'm now trying to use subqueries in the FROM clause to create two temporary tables and JOIN them as in the following:

SELECT
  I.custom_field_options.value.name AS Issue_name,
  C.custom_fields.value.value AS Issue_value
FROM
  (select custom_field_options from zendesk.ticket_fields) As I
JOIN
  (select custom_fields from zendesk.tickets) AS C
ON I.custom_field_options.value.value = C.custom_fields.value.value
WHERE C.custom_fields.value.id=360014264753

But this gives the following error: "Cannot access field value on a value with type ARRAY<STRUCT<value STRUCT<default BOOL, raw_name STRING, name STRING, ...>>> at [44:27]". I think I need to UNNEST the arrays but I can't figure out how to do it. Help would be much appreciated!

Have you checked this Stack thread?

Nick_Kh
– Nick_Kh

2020-07-07 16:27:53 +00:00
Commented Jul 7, 2020 at 16:27 — Nick_Kh
– Nick_Kh, Commented Jul 7, 2020 at 16:27

Maryam · Accepted Answer · 2020-07-08 07:12:36Z

0

which record is nested? If both costum fields are nested you should try this:

SELECT
  I.custom_field_options.value.name AS Issue_name,
  C.custom_fields.value.value AS Issue_value
FROM
  (select custom_field_options from zendesk.ticket_fields, unnest(custom_field_options) custom_field_options) As I
JOIN
  (select custom_fields from zendesk.tickets, unnest(custom_fields) custom_fields) AS C
ON I.custom_field_options.value.value = C.custom_fields.value.value
WHERE C.custom_fields.value.id=360014264753

answered Jul 8, 2020 at 7:12

Maryam

7301 gold badge7 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to access values from type ARRAY>STRUCT from within a subquery in Google BigQuery

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related