google bigquery - Create a table with Record type column -
i want create new table both record type columns , non record type column. table populated query.
this query:
select country, count(*) cnt_events,item,currency,sum(amount) sum_amount, count(transactionid) cnt_transactionid (select "us" country, "usd" currency, "book" item, 20 amount, 1 transactionid), (select "spain" country,"eur" currency, "book" item, 10 amount, 2 transactionid), (select "us" country,"usd" currency, "cup" item, 5 amount, 3 transactionid), (select "spain" country,"eur" currency, "notebook" item, 15 amount, 4 transactionid), (select "spain" country,"eur" currency, "notebook" item, 13 amount, 5 transactionid), (select "us" country, "null" currency, "null" item, null amount, null transactionid) group country, item, currency the schema:
[ {'name': 'country', 'type': 'string'}, {'name': 'cnt_events', 'type': 'integer'}, {'name': 'purchases', 'type': 'record', 'mode': 'repeated', 'fields': [ {'name': 'item', 'type': 'string'}, {'name': 'currency', 'type': 'string'}, {'name': 'sum_amount', 'type': 'integer'}, {'name': 'cnt_transactionid', 'type': 'integer'} ] }, ] and results:
country cnt_events purchases.item purchases.currency purchases.sum_amount purchases.cnt_transactionid 3 cup usd 5 1 book usd 20 1 spain 3 book eur 10 1 notebook eur 28 2 how can it? thanks!
below should work
select country, cnt_events, purchases.item, purchases.currency, purchases.sum_amount, purchases.cnt_transactionid js( ( // input table select country, sum(cnt_events) cnt_events, nest(concat(item, ',', currency, ',', string(sum_amount) , ',', string(cnt_transactionid))) purchases ( select country, count(1) cnt_events, item, currency, sum(amount) sum_amount, count(transactionid) cnt_transactionid (select "us" country, "usd" currency, "book" item, 20 amount, 1 transactionid), (select "spain" country,"eur" currency, "book" item, 10 amount, 2 transactionid), (select "us" country,"usd" currency, "cup" item, 5 amount, 3 transactionid), (select "spain" country,"eur" currency, "notebook" item, 15 amount, 4 transactionid), (select "spain" country,"eur" currency, "notebook" item, 13 amount, 5 transactionid), (select "us" country, "null" currency, "null" item, null amount, null transactionid) group country, item, currency ) group country ), country, cnt_events, purchases, // input columns "[ // output schema {'name': 'country', 'type': 'string'}, {'name': 'cnt_events', 'type': 'integer'}, {'name': 'purchases', 'type': 'record', 'mode': 'repeated', 'fields': [ {'name': 'item', 'type': 'string'}, {'name': 'currency', 'type': 'string'}, {'name': 'sum_amount', 'type': 'integer'}, {'name': 'cnt_transactionid', 'type': 'integer'} ] } ]", "function(row, emit) { // function var c = []; (var = 0; < row.purchases.length; i++) { x = row.purchases[i].split(','); t = {item:x[0], currency:x[1], sum_amount:parseint(x[2]), cnt_transactionid:parseint(x[3])} ; c.push(t); }; emit({country: row.country, cnt_events: row.cnt_events, purchases: c}); }" ) i think output expected:
[ { "country": "us", "cnt_events": "3", "purchases": [ { "item": "book", "currency": "usd", "sum_amount": "20", "cnt_transactionid": "1" }, { "item": "cup", "currency": "usd", "sum_amount": "5", "cnt_transactionid": "1" } ] }, { "country": "spain", "cnt_events": "3", "purchases": [ { "item": "notebook", "currency": "eur", "sum_amount": "28", "cnt_transactionid": "2" }, { "item": "book", "currency": "eur", "sum_amount": "10", "cnt_transactionid": "1" } ] } ] 
Comments
Post a Comment