E
E
Evgeny Rebrov2019-06-24 12:46:53
Google Cloud Platform
Evgeny Rebrov, 2019-06-24 12:46:53

Unnest in several nesting levels - how to apply it correctly?

Can you tell me how to correctly apply unnest for two or more levels of nesting? I will explain with an example. There is the following scheme:

tables in google bigquery
Название поля	Тип	Режим	Описание

<b>WebsiteId	</b>STRING	NULLABLE	
<b>Status	</b>STRING	NULLABLE	
<b>StatusDate	</b>DATETIME	NULLABLE	
<b>CreatedAt	</b>DATETIME	NULLABLE	
<b>RegionID	</b>INTEGER	NULLABLE	
<b>CouponNumber	</b>STRING	NULLABLE	
<b>TotalPrice	</b>FLOAT	NULLABLE	
<b>Items	</b>RECORD	REPEATED	
<b>Items. SKU</b>	STRING	NULLABLE	
<b>Items. Guid</b>	STRING	NULLABLE	
<b>Items. Status	</b>STRING	NULLABLE	
<b>Items. StatusDate</b>	DATETIME	NULLABLE	
<b>Items. Price</b>	FLOAT	NULLABLE	
<b>Items. DiscountInfo</b>	RECORD	NULLABLE	
<b>Items.DiscountInfo. DiscountPercent</b>	FLOAT	NULLABLE	
<b>Items.DiscountInfo. Code</b>	STRING	NULLABLE

If I just needed to pull out the SKU from Items, then it was like this: UNNEST(Items) as I where I.SKU like 'nnnn'
However, now I need it from Items.DiscountInfo. Code to pull out .Code, and I can’t figure out how to formulate the request correctly.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question