CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
Fardream
Fardream

Reputation: 664

Big Query Create View with Repeated Record

We have a series of tables with schema that contains a repeated record, like follows:

[{
    name: "field1",
    type: "RECORD",
    mode: "REPEATED",
    fields: [{type: "STRING", name: "subfield1"}, {type: "INTEGER", name: "subfield2"}]
}]

when we create a view that include that repeated record field, we always get error:

Error in query string: Field field1 from table xxxxx is not a leaf field.

I understand that it might be better to use flatten, but all this field contains mostly different filters we want to test on and we have a lot of other non-repeated fields that would be difficult to manage if flattened.

It turned out that the problem is selecting the repeated record field from multiple tables (not in creating view). Is there an easy way to get around that?

Thanks

Upvotes: 0

Views: 643

Answers (1)

Jordan Tigani
Jordan Tigani

Reputation: 26617

If you do SELECT field.* from t1, t2 you'll get an error that the * cannot be used to refer fields in a union (as you've noticed above).

You can work around this by wrapping the union in an inner SELECT statement, as in SELECT field.* from (SELECT * from t1, t2).

To give a concrete example, this works:

SELECT payload.pages.* 
FROM (
    SELECT *  
    FROM [publicdata:samples.github_nested],   
         [publicdata:samples.github_nested])

Upvotes: 1

Related Questions