将STRUCT的ARRAY传递给标准BigQuery SQL的用户定义函数

如何将结构数组传递到用户定义的函数中（使用标准SQL）？

首先，一些上下文：

表架构：

id STRING
customer STRING
request STRUCT<
  headers STRING
  body STRING
  url STRING
>
response STRUCT<
  size INT64
  body STRING
>
outgoing ARRAY<
  STRUCT<
    request STRUCT<
      url STRING,
      body STRING,
      headers STRING
    >,
    response STRUCT<
      size INT64,
      body STRING
    >
  >
>

用户定义的功能：

CREATE TEMPORARY FUNCTION extractDetailed(
  customer STRING,
  request STRUCT<
    headers STRING,
    body STRING
  >,
  outgoing ARRAY<
    STRUCT<
      request STRUCT,
      response STRUCT
    >
  >
)
RETURNS STRING
LANGUAGE js AS """

""";

SELECT extractDetailed(customer, STRUCT(request.headers, request.body), outgoing)
FROM request_logs

关于我的问题：我似乎无法弄清楚如何选择一部分outgoingARRAY，并将其作为数组传递给用户定义的函数。

实际上，我正在尝试模拟以下用户定义的函数调用：

extractDetailed(
  "customer id",
  { "headers": "", "body": "" },
  [
    {
      "request": { "url": "" },
      "response": { "body": "" }
    },
    {
      "request": { "url": "" },
      "response": { "body": "" }
    }
  ]
);

最近，我偶然发现了一些可能有助于解锁的文档，但我似乎无法弄清楚如何使其合适。我真的为此感到挣扎，并且希望能为解决该问题提供帮助。

请尝试以下。它从数组中解析所需的和平，并在传递给函数之前将它们放回到新的数组中，以使其与征兆匹配

CREATE TEMPORARY FUNCTION extractDetailed(
customer STRING,
request STRUCT,
outgoing ARRAY, response STRUCT>>
)
RETURNS STRING
LANGUAGE js AS """

""";

SELECT 
  extractDetailed(
    customer, 
    STRUCT(request.headers, request.body), 
    ARRAY(
      SELECT STRUCT,response STRUCT>
          (STRUCT(request.url), STRUCT(response.body)) 
      FROM UNNEST(outgoing)
    )
  ) AS details
FROM request_logs  

为了进一步“优化”上面的查询并使它更可移植，您可以将提取的部分从原始数组包装到新数组中，并将其包装到单独的SQL UDF中

CREATE TEMPORARY FUNCTION extractParts (
  outgoing ARRAY,
                        response STRUCT>>
)
RETURNS ARRAY, response STRUCT>>
AS ((
  SELECT ARRAY(
      SELECT STRUCT,response STRUCT>
          (struct(request.url), struct(response.body)) 
      FROM UNNEST(outgoing)
    )
));

CREATE TEMPORARY FUNCTION extractDetailed(
  customer STRING,
  request STRUCT,
  outgoing ARRAY, response STRUCT>>
)
RETURNS STRING
LANGUAGE js AS """
  return outgoing.length;
""";

SELECT 
  extractDetailed(
    customer, 
    STRUCT(request.headers, request.body),
    extractParts(outgoing)
  ) as details
FROM request_logs