BigQuery关联字段与JSON展开仅返回左表数据问题求助

原SQL的核心问题 #

• 分组逻辑打乱关联关系：json_answers里用GROUP BY 5,6按Impact_topic_text和Impact_reply_get分组，直接破坏了Interview_ID与原表Impact_Question_id的对应关系，再加上ANY_VALUE随机取值，导致后续关联完全失效。
• 冗余代码无意义：Impact_Question_id_TBL这个CTE定义后从未使用，属于无效代码。
• 结果只取左表字段：最终查询只选择了左表的Impact_Question_id，就算关联成功也看不到JSON展开的右表数据。
• 关联条件可靠性低：Interview_ID是从Impact_antwort_id拆分清洗而来，分组后无法保证和原表Impact_Question_id的一一对应。

修正后的SQL语句 #

#standardSQL
CREATE TEMP FUNCTION jsonunnest(input STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(input).map(j=>JSON.stringify(j));
""";

WITH `Impact_JSON` AS (
  SELECT 
    Impact_Question_id,
    Impact_Question_text, 
    json,
    ROW_NUMBER() OVER (PARTITION BY bdmp_id, DATE(Impact_Question_aktualisiert_am_ts)
        ORDER BY Impact_Question_aktualisiert_am_ts DESC) AS row_num
  FROM `<project.dataset.table>` basetable
  -- 直接过滤每组最新记录，避免后续处理重复数据
  QUALIFY row_num = 1
),
json_answers AS (
  SELECT
    -- 携带原表主键，确保关联准确
    T.Impact_Question_id,
    REGEXP_REPLACE(SPLIT(JSON_EXTRACT_SCALAR(Impact, '$.Impact_antwort_id'), '_')[SAFE_OFFSET(1)], "[^0-9]+", "") AS Interview_ID,
    REGEXP_REPLACE(SPLIT(JSON_EXTRACT_SCALAR(Impact, '$.Impact_antwort_id'), '_')[SAFE_OFFSET(3)], "[^0-9]+", "") AS Quest_ID,
    JSON_EXTRACT_SCALAR(Impact, '$.Impact_antwort_id') AS Impact_antwort_id,
    JSON_EXTRACT_SCALAR(Impact, '$.Impact_antwort_daten_typ') AS Impact_reply_data_type,
    IFNULL(JSON_EXTRACT_SCALAR(Impact, '$.Impact_topic_text'), 'Empty') AS Impact_topic_text,
    IFNULL(JSON_EXTRACT_SCALAR(Impact, '$.Impact_reply_get'), 'Empty') AS Impact_reply_get
  FROM `Impact_JSON` T,
  UNNEST(jsonunnest(JSON_EXTRACT(json, '$.reply'))) Impact
  -- 若需去重聚合，取消下方注释并调整逻辑
  -- GROUP BY T.Impact_Question_id, Interview_ID, Quest_ID, Impact_topic_text, Impact_reply_get
)

-- 左关联保留所有左表记录，同时获取JSON展开数据
SELECT
  T.Impact_Question_id,
  T.Impact_Question_text,
  J.Interview_ID,
  J.Quest_ID,
  J.Impact_antwort_id,
  J.Impact_reply_data_type,
  J.Impact_topic_text,
  J.Impact_reply_get
FROM `Impact_JSON` T
LEFT JOIN json_answers J
ON T.Impact_Question_id = J.Impact_Question_id

修正说明 #

1. 在Impact_JSON中用QUALIFY row_num = 1直接过滤出每组最新记录，减少后续重复数据处理。
2. json_answers携带原表的Impact_Question_id，关联时直接用该字段匹配，保证对应关系准确。
3. 去掉错误的分组和随机取值函数，默认保留UNNEST后的每条JSON行；若需去重聚合，再启用GROUP BY并调整聚合逻辑。
4. 最终查询同时选取左表和右表字段，确保能同时获取常规文本记录与JSON展开结果。

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