Menu
I have a schema that contains multiple repeated fields which are not nested.
I'm trying to query the cross product, but I get an error: "Cannot query the cross product of repeated fields..."
If I query only 2 repeated fields, I can flatten one of them. Yet, I'm interested in querying more that 2 repeated fields, and I can't understand how FLATTEN syntax supports this.
For example, say the table structure is: a1, record (repeated) : a1.b1, integer a2, record (repeated) : a2.b1, integer a3, record (repeated) : a3.b1, integer
I want to query: select (*) from tab
324
How to Query BigQuery Repeated Fields. To extract information from a repeated field in BigQuery, you must use a more exotic pattern. This is normally done using the UNNEST function, which converts an array of values in a table into rows. These can then be joined to the original table to be queried.
A repeated field can be accessed as an ARRAY type in Google Standard SQL. A RECORD column can have REPEATED mode, which is represented as an array of STRUCT types. Also, a field within a record can be repeated, which is represented as a STRUCT that contains an ARRAY . An array cannot contain another array directly.
Using nested and repeated fieldsQuerying nested data uses "dot" syntax to reference leaf fields, which is similar to the syntax using a join. Nested data is represented as a STRUCT type in Google Standard SQL.
BigQuery automatically flattens nested fields when querying. To query a column with nested data, each field must be identified in the context of the column that contains it. For example: customer.id refers to the id field in the customer column.
Now that BigQuery has moved to Standard SQL, using FLATTEN doesn't work. However Google has documented how to migrate. This solution worked for me, although there are several other ways to do it:
SELECT
flattened_field_1,
flattened_field_2
FROM my_dataset.my_table
LEFT JOIN UNNEST(repeated_field_1) AS flattened_field_1
LEFT JOIN UNNEST(repeated_field_2) AS flattened_field_2
# ...etc
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
