创建一个列类型为RECORD的表格 [英] create a table with a column type RECORD

问题描述

我使用的是大查询，我想创建一个填充记录类型列的表。
数据将被查询填充 - 那么如何编写一个返回记录类型列的查询。

谢谢！

解决方案

不知何故，Pentium10提出的选项从来没有在GBQ UI或API Explorer中为我工作。

我可能缺少某些东西

同时，我发现的解决方法如下例所示：

  SELECT位置.state，location.city FROM JS（
（//输入表
 SELECT NEST（CONCAT（state，'，'city））AS位置
 FROM（
 SELECT状态，城市从
（SELECT'florida'AS州，'miami'AS city），
（SELECT'california'AS state，'la'AS city），
（SELECT'romania' AS state，'transylvania'AS city）
）
），
 locations，//输入栏
[//输出模式
 {'name'：'位置'，'类型'：'RECORD'，
'mode'：'REPEATED'，
'fiel ds'：[
 {'name'：'state'，'type'：'STRING'}，
 {'name'：'city'，'type'：'STRING'} 
 
 
，
function（row，emit）{//函数
 for（var i = 0;我< row.locations.length; i ++）{
 var c = []; 
 x = row.locations [i] .split（'，'）; 
 t = {state：x [0]，city：x [1]} 
 c.push（t）; 
 emit（{location：c}）; 
}; 
}
）
  

请注意：

您应该在允许大型结果并取消选中展平结果

输出表的结果是（在JSON模式下）

  [
 {
location ：[
 {
state：california，
city：la
} 
] 
}，
 {
location：[
 {
state：florida，
city：miami
} 
] 
 $ blocation：[
 {
state：romania，
city：transylvania
 
 
 
 
 
 
 $ blockquote 
 
添加到解决某些问题@AdiCohen以他真实的例子显示他在最近的评论中显示
：
 
 
问：我的查询还有其他专栏记录列，但是当我运行
查询时，th ey返回为null。我如何创建一个表
类型的表？
 
 
 
 
 
  SELECT金额，货币，location.state，location.city FROM JS（
（//输入表
 SELECT NEST（CONCAT（state，'，'city））AS位置
 SUM ）AS amount，MAX（currency）as currency 
 FROM（
 SELECT state，city，amount，currency，ROW_NUMBER（）OVER（）as grp FROM 
（SELECT'florida'AS state， 'ASAM'，AS'city'，'coin'AS currency，40 AS amount，
（SELECT'california'AS state，'la'AS city，'coins'AS currency，40 AS amount），
 （SELECT'romania'AS state，'transylvania'AS city，'coins'AS currency，40 AS amount）
）GROUP BY grp 
），
 amount，currency，locations，//输入列
'[//输出模式
''名称'：'location'，'type'：'RECORD'，'mode'：'REPEATED'，
'fields'：[ 
 {'名字：'state'，'type'：'STRING'}，
 {'name'：'city'，'type'：'STRING'} 
]}，
 {'name' ：'amount'，'type'：'INTEGER'}，
''name'：'currency'，'type'：'STRING'} 
]，
function（row ，发出）{//函数
 for（var i = 0;我< row.locations.length; i ++）{
 var c = []; 
 x = row.locations [i] .split（'，'）; 
 t = {state：x [0]，city：x [1]} 
 c.push（t）; 
 emit（{amount：row.amount，currency：row.currency，location：c}）; 
}; 
}
）
  
这里的输出是：
  [
 {
amount：40，
currency：coins，
location_state：罗马尼亚，
location_city：transylvania
}，
 {
金额：40，
货币：硬币，
location_state：佛罗里达，
location_city：miami
}，
 {
金额：40 ，
currency：coins，
location_state：california，
location_city：la
} 
] 
  
 
I'm using big query and i want to create a job which populates a table with a "record" type columns.
The data will be populated by a query - so how can i write a query which returns "record" type columns.

Thanks!
解决方案
Somehow option proposed by Pentium10 never worked for me in GBQ UI or API Explorer.

I might be missing something

Meantime, the workaround I found is as in below example
SELECT location.state, location.city FROM JS(
  (      // input table
  SELECT NEST(CONCAT(state, ',', city)) AS locations
  FROM (
    SELECT state, city FROM 
    (SELECT 'florida' AS state, 'miami' AS city),
    (SELECT 'california' AS state, 'la' AS city),
    (SELECT 'romania' AS state, 'transylvania' AS city)
    ) 
  ),
  locations,     // input columns
  "[    // output schema
    {'name': 'location', 'type': 'RECORD',
     'mode': 'REPEATED',
     'fields': [
       {'name': 'state', 'type': 'STRING'},
       {'name': 'city', 'type': 'STRING'}
     ]    
    }
  ]",
  "function(row, emit){    // function 
    for (var i = 0; i < row.locations.length; i++) {
      var c = [];
      x = row.locations[i].split(',');
      t = {state:x[0], city:x[1]}
      c.push(t);
      emit({location: c});  
    };
  }"
)  
Please note:

you should set destination table with Allow Large Results and unchecked Flatten Results  

Result from output table is (in JSON Mode)  
[
  {
    "location": [
      {
        "state": "california",
        "city": "la"
      }
    ]
  },
  {
    "location": [
      {
        "state": "florida",
        "city": "miami"
      }
    ]
  },
  {
    "location": [
      {
        "state": "romania",
        "city": "transylvania"
      }
    ]
  }
]



  
Added to address some issue @AdiCohen has with his real example that
  he showed in his recent comments:   
  
  
Q: my query has other columns besides the record column, but when i ran
  the query, they return as null. how can i create a table with both of
  the types?


SELECT amount, currency, location.state, location.city FROM JS( 
  ( // input table 
    SELECT NEST(CONCAT(state, ',', city)) AS locations, 
      SUM(amount) AS amount, MAX(currency) as currency 
    FROM ( 
      SELECT state, city, amount, currency, ROW_NUMBER() OVER() as grp FROM 
        (SELECT 'florida' AS state, 'miami' AS city, 'coins' AS currency, 40 AS amount), 
        (SELECT 'california' AS state, 'la' AS city, 'coins' AS currency, 40 AS amount), 
        (SELECT 'romania' AS state, 'transylvania' AS city,'coins' AS currency, 40 AS amount) 
    ) GROUP BY grp
  ), 
  amount, currency, locations, // input columns 
  "[ // output schema 
    {'name': 'location', 'type': 'RECORD', 'mode': 'REPEATED', 
    'fields': [ 
      {'name': 'state', 'type': 'STRING'}, 
      {'name': 'city', 'type': 'STRING'} 
    ] }, 
    { 'name': 'amount', 'type': 'INTEGER'}, 
    { 'name': 'currency', 'type': 'STRING'} 
  ]", 
  "function(row, emit) { // function 
    for (var i = 0; i < row.locations.length; i++) { 
      var c = []; 
      x = row.locations[i].split(','); 
      t = {state:x[0], city:x[1]} 
      c.push(t); 
      emit({amount: row.amount, currency: row.currency, location: c}); 
    }; 
  }"
) 
Output here is:
[
  {
    "amount": "40",
    "currency": "coins",
    "location_state": "romania",
    "location_city": "transylvania"
  },
  {
    "amount": "40",
    "currency": "coins",
    "location_state": "florida",
    "location_city": "miami"
  },
  {
    "amount": "40",
    "currency": "coins",
    "location_state": "california",
    "location_city": "la"
  }
]


                        
这篇关于创建一个列类型为RECORD的表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！