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"       }     ]   } ]