如何编写BigQuery查询从Person表提取指定格式数据？

现有BigQuery中名为Person的表包含以下5条嵌套结构的记录：

[
    {    
        "birthDate": "2024-01-01",    
        "name": [
            {        
                "given": "John",        
                "family": "Wayne"    
            }
        ],    
        "link": [
            {        
                "target": {          
                    "patientId": "patient12345",          
                    "personId": null,          
                    "type": "Patient"        
                }    
            },
            {        
                "target": {          
                    "patientId": null,          
                    "personId": "personABCD",          
                    "type": "Person"        
                }    
            }
        ]
    },
    {
        "birthDate": "2023-01-01",    
        "name": [
            {        
                "given": "Jane",        
                "family": "Wayne"    
            }
        ],    
        "link": [
            {        
                "target": {          
                    "patientId": "patient45345",          
                    "personId": null,          
                    "type": "Patient"        
                }    
            },
            {        
                "target": {          
                    "patientId": null,          
                    "personId": "personXCFD",          
                    "type": "Person"        
                }    
            }
        ]
    },
    {
        "birthDate": "2022-01-01",    
        "name": [
            {        
                "given": "Jill",        
                "family": "Wayne"    
            }
        ],    
        "link": [
            {        
                "target": {          
                    "patientId": null,          
                    "personId": "personFWWA",          
                    "type": "Person"        
                }    
            }
        ]
    },
    {
        "birthDate": "2021-01-01",    
        "name": [
            {        
                "given": "Jack",        
                "family": "Wayne"    
            }
        ],    
        "link": [
            {        
                "target": {          
                    "patientId": "patient0942",          
                    "personId": null,          
                    "type": "Patient"        
                }    
            }
        ]
    },
    {
        "birthDate": "2020-01-01",    
        "name": [
            {        
                "given": "Jim",        
                "family": "Wayne"    
            }
        ],    
        "link": []
    }
]

需要将数据提取为以下格式：

given, family, patientId, personId
----------------------------------
John, Wayne, patient12345, personABCD
Jane, Wayne, patient45345, personXCFD
Jill, Wayne, null, personFWWA
Jack, Wayne, patient0942, null
Jim, Wayne, null, null

查询语句 #

使用UNNEST展开嵌套数组，结合条件聚合提取对应ID，同时用LEFT JOIN避免空数组记录被过滤：

SELECT
  name.given,
  name.family,
  MAX(IF(link.target.type = 'Patient', link.target.patientId, NULL)) AS patientId,
  MAX(IF(link.target.type = 'Person', link.target.personId, NULL)) AS personId
FROM
  `your-project.your-dataset.Person`,
  UNNEST(name) AS name
LEFT JOIN UNNEST(link) AS link
ON TRUE
GROUP BY
  name.given, name.family

关键逻辑说明 #

• 展开姓名数组：UNNEST(name)直接提取given和family，因为每条记录的姓名数组只有一条数据。
• 保留空链接记录：LEFT JOIN UNNEST(link)确保即使link数组为空，主记录也不会被过滤。
• 提取对应类型ID：通过MAX(IF(...))的条件聚合，分别筛选出Patient类型的patientId和Person类型的personId，同类型多条记录会保留非空值。

如果确认每条记录的link数组中同类型ID最多只有一个，用MAX或ANY_VALUE效果一致。

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