How to query record type repeated field in google big query?

1
votes

In my big query table, I just added another record type field say "B" inside record type field "A". In standard SQL dialect, I am unable to query repeated fields of B as- select A.B.field1, A.B.field2, ... from where _partitiontime = ;

for following query-

SELECT ad_request_custom_targeting.iom.size, 
ad_request_custom_targeting.iom.bidprice FROM `nyt-adtech- 
prd.dfp_data.dfp_log_network_requests` WHERE DATE(_PARTITIONTIME) = "2019- 
05-03" LIMIT 1000

I get error as- Cannot access field size on a value with type "ARRAY>" at [1:40]

1
google-cloud-platformgoogle-bigquery

1 Answers

2
votes

For BigQuery Standard SQL

I assume that in your example

A is ad_request_custom_targeting and B is iom 

SELECT B.size, B.bidprice 
FROM `nyt-adtech-prd.dfp_data.dfp_log_network_requests`,
  UNNEST(ad_request_custom_targeting) A,
  UNNEST(A.iom) B 
WHERE DATE(_PARTITIONTIME) = "2019-05-03" 
LIMIT 1000