I think conceptually you misunderstood something. It's not that you can't reference 3 levels deep. It's about unnesting is for arrays. What you did @4:54 with referencing into the second level is because that's actually a struct. You can see that in the schema. REPEATED means Array, RECORD means struct. In general what happens though that you have an array of structs. I think that's why you came up with the idea that the 3rd level needs to be unnested. I'm just leaning big query as well. So correct me if I'm wrong :)
Hello Thank you for the course, i wanted to unnest traffic but the error message said it need to be a string. I wanted to know the difference between hits and traffic is because hits is repeated not nullable?
I think conceptually you misunderstood something. It's not that you can't reference 3 levels deep. It's about unnesting is for arrays. What you did @4:54 with referencing into the second level is because that's actually a struct.
You can see that in the schema. REPEATED means Array, RECORD means struct.
In general what happens though that you have an array of structs. I think that's why you came up with the idea that the 3rd level needs to be unnested.
I'm just leaning big query as well. So correct me if I'm wrong :)
You may be right! I just do what gets the job done :)
Can iHave your email? wanna ask a question if you don't mind
Thank you for This series
God bless you
I have a question. Why didn't you unnest Totals. It's also a record. Why unnest only hits. What's the difference?
Hey can you write unnest query that will flatten nested array or array inside array as well.
Awesome video! Thanks for the good work!
Glad you liked it!
What's the difference between CROSS JOIN and ","? It'll get different results? Or it is just another way to do the same thing?
Hmm not sure I follow Thiago, can you drop an example here?
I want to hug you. Thank you.
No need to perform a join on nested data, you can just select from unnest.
Hello
Thank you for the course, i wanted to unnest traffic but the error message said it need to be a string. I wanted to know the difference between hits and traffic is because hits is repeated not nullable?
Hey Jurge, sorry I'm not 100% sure what you're referring to there, could you ask that another way?
how to handle this error. new to bigquery. please help.
error : Column name name is ambiguous at [2:1]
add alias