如何在BigQuery中通过多表连接生成指定JSON对象？

现有company、employee、employee_address三张表，数据关系为：一个公司对应多个员工，一个员工对应多个地址。需编写BigQuery查询语句生成指定结构的JSON对象。

表结构与测试数据SQL #

create table company
(
company_id string,
comapnay_name string
);
insert into  company values("C1","TCSE");

create table employee
(
company_id string,
employee_id string,
employee_type string
);
insert into employee values("C1","RP1","Principal"),
("C1","RP11","CEO");

create table employee_address
(
company_id string,
employee_id string,
employee_address_id string,
adrees_detail_text string
);
insert into employee_address values
("C1","RP1","RP1A1","kadapa"),
("C1","RP1","RP1A2","B mattam"),
("C1","RP11","RP11A1","kadapa");

预期JSON输出 #

[
    {"company_id":"C1",
    "comapnay_name":"TCSE",
    "empdetails":[
    {
    "employee_id":"RP11",
    "employee_type":"CEO",
    "employeeaddress":[
    {
        "company_id":"C1",
        "employee_id":"RP11",
        "employee_address_id":"RP11A1",
        "adrees_detail_text":"kadapa"
    }
    ]
        },
        
    {
        "employee_id":"RP1",
        "employee_type":"Principal",
        "employeeaddress":[
        {
        "company_id":"C1",
        "employee_id":"RP1",
        "employee_address_id":"RP1A1",
        "adrees_detail_text":"kadapa"
        },
        {"company_id":"C1",
        "employee_id":"RP1",
        "employee_address_id":"RP1A2",
        "adrees_detail_text":"B mattam"
        }
        ]
        }
        ]
    }
    ]

用户尝试的查询（未得到预期结果） #

with emp_add_array as
(select company_id,employee_id,array_agg(to_json_string(emp_addr)) as cemp_addr_array 
from employee_address  as emp_addr group by  company_id,employee_id ),
emp_emp_addr as
(select emp.company_id,emp.employee_id,emp.employee_type,cemp_addr_array as employeeaddress
from  employee emp left outer join  emp_add_array on 
emp.employee_id=emp_add_array.employee_id
 ),
 cmp_emp_addr_all as
 ( select company_id,array_agg(to_json_string(empaddrall)) as empaddrdetails from emp_emp_addr as empaddrall group by company_id),
cmpall as
(
select cmp.company_id,comapnay_name,empaddrdetails
from company as cmp left outer join cmp_emp_addr_all on cmp.company_id=cmp_emp_addr_all.company_id
)  select company_id,array_agg(to_json_string(t)) as employee  from cmpall as t group by company_id 

最优BigQuery查询语句 #

SELECT TO_JSON_STRING(ARRAY_AGG(company_data)) AS result
FROM (
    SELECT
        c.company_id,
        c.comapnay_name,
        ARRAY_AGG(STRUCT(
            e.employee_id,
            e.employee_type,
            (SELECT ARRAY_AGG(STRUCT(
                ea.company_id,
                ea.employee_id,
                ea.employee_address_id,
                ea.adrees_detail_text
            )) FROM employee_address ea 
             WHERE ea.company_id = e.company_id AND ea.employee_id = e.employee_id) AS employeeaddress
        )) AS empdetails
    FROM company c
    LEFT JOIN employee e ON c.company_id = e.company_id
    GROUP BY c.company_id, c.comapnay_name
) company_data;

思路说明 #

1. 针对每个员工，通过子查询聚合其对应的地址记录为结构化数组employeeaddress；
2. 将员工信息与地址数组封装为STRUCT，再聚合为每个公司的员工详情数组empdetails；
3. 最后将公司信息与员工详情数组封装为公司对象，聚合为整体数组后转成JSON字符串，得到符合要求的嵌套结构。

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