BigQuery构造指定JSON输出时STRUCT类型不匹配问题求助

核心错误原因 #

你的查询报错本质是临时表schema与插入数据的结构完全不匹配：

1. 临时表定义的keys是ARRAY<STRUCT<EVENT_TYPE_CODE STRING, EVENT_TYPE_DESCRIPTION STRING, METADATA STRUCT<subKeyName STRING, subKeyType STRING>>>，但插入时构造的是匿名结构体数组，且元素类型（字符串+字符串+数组）与定义的命名结构体类型不兼容。
2. 你给出的目标输出中，keys是字符串数组，但临时表错误地定义为结构体数组，从根源上偏离了需求。

方案1：直接生成目标格式JSON（无需临时表） #

如果你的目标是生成示例中的JSON结构，无需临时表，直接构造即可：

#standardSQL
SELECT TO_JSON_STRING(STRUCT(
  'SelectionListName' AS selectionListId,
  ['EVENT_TYPE_CODE', 'EVENT_TYPE_DESCRIPTION', 'METADATA'] AS keys,
  'dataTopicSelectionList' AS fileType,
  -- 若需要将聚合后的METADATA作为结构体包含，添加以下字段
  STRUCT(
    MIN(DATE(TransferLoc.EVENT_DTM)) AS CountedFrom,
    MAX(DATE(TransferLoc.EVENT_DTM)) AS To_,
    COUNT(TransferLoc.EVENT_DTM) AS Count
  ) AS METADATA
)) AS output_json
FROM `projectID.TransferLoc` TransferLoc
INNER JOIN `projectID.TransferEventType` TransferEventType
  ON TransferLoc.EVENT_TYPE_DK = TransferEventType.EVENT_TYPE_DK
WHERE 
  TransferLoc.EVENT_DTM > '1900-01-01 00:00:00' AND 
  TransferLoc.EVENT_DTM < CURRENT_DATE()
GROUP BY 
  TransferEventType.EVENT_TYPE_CODE,
  TransferEventType.EVENT_TYPE_DESCRIPTION;

方案2：修正临时表后插入数据（若必须使用临时表） #

如果需要通过临时表中转，先对齐schema与目标结构，再正确插入数据：

#standardSQL
-- 修正临时表schema：keys为字符串数组，按需添加METADATA结构体字段
CREATE OR REPLACE TEMP TABLE temp_table (
  selectionListId STRING,
  keys ARRAY<STRING>,
  fileType STRING,
  METADATA STRUCT<CountedFrom DATE, To_ DATE, Count INT64>
);

-- 插入匹配结构的数据
INSERT INTO temp_table (
  selectionListId,
  keys,
  fileType,
  METADATA
)
SELECT 
  'SelectionListName' AS selectionListId,
  ['EVENT_TYPE_CODE', 'EVENT_TYPE_DESCRIPTION', 'METADATA'] AS keys,
  'dataTopicSelectionList' AS fileType,
  STRUCT(
    MIN(DATE(TransferLoc.EVENT_DTM)) AS CountedFrom,
    MAX(DATE(TransferLoc.EVENT_DTM)) AS To_,
    COUNT(TransferLoc.EVENT_DTM) AS Count
  ) AS METADATA
FROM `projectID.TransferLoc` TransferLoc
INNER JOIN `projectID.TransferEventType` TransferEventType
  ON TransferLoc.EVENT_TYPE_DK = TransferEventType.EVENT_TYPE_DK
WHERE 
  TransferLoc.EVENT_DTM > '1900-01-01 00:00:00' AND 
  TransferLoc.EVENT_DTM < CURRENT_DATE()
GROUP BY 
  TransferEventType.EVENT_TYPE_CODE,
  TransferEventType.EVENT_TYPE_DESCRIPTION;

-- 输出最终JSON
SELECT TO_JSON_STRING(temp_table) AS output_json
FROM temp_table;

方案3：生成字段元数据结构体数组（若你的实际需求是描述字段类型） #

如果keys需要包含字段类型、子字段等元数据，需正确定义嵌套结构体：

#standardSQL
CREATE OR REPLACE TEMP TABLE temp_table (
  selectionListId STRING,
  keys ARRAY<STRUCT<
    keyName STRING,
    keyType STRING,
    subKeys ARRAY<STRUCT<subKeyName STRING, subKeyType STRING>> OPTIONAL
  >>,
  fileType STRING
);

INSERT INTO temp_table (
  selectionListId,
  keys,
  fileType
)
SELECT 
  'SelectionListName' AS selectionListId,
  [
    STRUCT('EVENT_TYPE_CODE' AS keyName, 'STRING' AS keyType, NULL AS subKeys),
    STRUCT('EVENT_TYPE_DESCRIPTION' AS keyName, 'STRING' AS keyType, NULL AS subKeys),
    STRUCT('METADATA' AS keyName, 'STRUCT' AS keyType, [
      STRUCT('CountedFrom' AS subKeyName, 'DATE' AS subKeyType),
      STRUCT('To_' AS subKeyName, 'DATE' AS subKeyType),
      STRUCT('Count' AS subKeyName, 'INT64' AS subKeyType)
    ] AS subKeys)
  ] AS keys,
  'dataTopicSelectionList' AS fileType
FROM (SELECT 1); -- 生成单行数据

-- 输出JSON
SELECT TO_JSON_STRING(temp_table) AS output_json
FROM temp_table;

内容的提问来源于stack exchange，提问作者D4M3