在Google BigQuery中如何关联含数组字段的Patients表与Forms表？

核心思路 #

Patients表的related_forms_ids是数组类型，需先通过UNNEST()函数将数组拆分为独立行，再与Forms表通过formId关联，最后可根据需求选择保留单行明细或聚合回数组格式。

方式一：拆分行获取表单明细 #

该方式会将每个患者的每个关联表单拆分为单独一行，适合查看明细场景：

SELECT
  p.*,
  f.formName
FROM
  `your-project.your-dataset.Patients` p,
  UNNEST(p.related_forms_ids) AS formId
JOIN
  `your-project.your-dataset.Forms` f
ON
  formId = f.formId

注意：替换your-project.your-dataset为你实际的BigQuery项目ID和数据集名称。

方式二：聚合表单名称为数组 #

若需保留每个患者一行记录，并将关联表单名称聚合成数组，可使用如下查询：

SELECT
  p.* EXCEPT(related_forms_ids),
  ARRAY_AGG(f.formName) AS related_form_names
FROM
  `your-project.your-dataset.Patients` p,
  UNNEST(p.related_forms_ids) AS formId
JOIN
  `your-project.your-dataset.Forms` f
ON
  formId = f.formId
GROUP BY
  p.* EXCEPT(related_forms_ids)

通过ARRAY_AGG()聚合表单名称，EXCEPT(related_forms_ids)用于排除原数组字段，避免结果重复。

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