sql - 在 BigQuery 标准 SQL 中将 DATETIME 转换为 STRING，处理 STRUCT 的 ARRAY

Question

在我的表中，我有一个名为“消息”的属性，其数据类型完全相同:

ARRAY<STRUCT<created_time  DATETIME ,`from` STRUCT<id STRING, 
name STRING,email STRING>, id STRING, message STRING>>

并且我已经定义了一个名为 my_func() 的 UDF

因为 Big Query 中的 UDF 函数不支持 DATETIME 类型，所以我需要转换属性 created_time。

所以我试了一下:

safe_cast ( messages as ARRAY<STRUCT<created_time  STRING ,
'from` STRUCT<id STRING, name STRING, email STRING>,
id STRING, message STRING>>) as messages_casted 

我得到了这个错误

Casting between arrays with incompatible element types is not
supported: Invalid cast from...

我转换数组结构的方式有误吗？

有一些方法可以将 UDF 用于此数据结构，还是唯一的方法是展平数组并进行转换？

我的目标是在JS执行环境中获取数组，以便用JS代码进行聚合。

Answer 1

使用 JavaScript UDF 时，您无需明确指定复杂的输入数据类型。相反，您可以使用 TO_JSON_STRING function .在您的情况下，您可以让 UDF 将 messages 作为 STRING，然后在 UDF 中解析它。例如，您可以调用 my_func(TO_JSON_STRING(messages))。

这是一个例子 from the documentation :

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS """
function SumFoo(obj) {
  var sum = 0;
  for (var field in obj) {
    if (obj.hasOwnProperty(field) && obj[field] != null) {
      if (typeof obj[field] == "object") {
        sum += SumFoo(obj[field]);
      } else if (field == "foo") {
        sum += obj[field];
      }
    }
  }
  return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+