BigQuery SQL -向现有记录添加新记录

Question

我有两个表，我想创建一个UNION，但它们没有相同的模式。

表1如下所示:表1

表2如下所示:表2

如何选择表2的所有数据并添加details.employment.locations city,regionCode和格式化的记录?我只想使用BigQuery SQL为所有这些包含空值。

谢谢你,

Answer 1

应该可以。根据您的模式，我已经为table1和table2创建了ctes。既然已经有了表，就可以从UNIONED开始查询cte

WITH  TABLE1 AS
(
SELECT 
'WEBSITE 1' AS WEBSITE,
STRUCT (
STRUCT ('NAME 1 ') AS NAME,
STRUCT ('AGE 1') AS AGE,
'GENDER' AS GENDER,
STRUCT(
'EMPLOYEMENT NAME 1' AS NAME,
FALSE AS _CURRENT,
'EMPLOYEMENT TITLE 1' AS TITLE,
STRUCT(
'EMPLOYEMENT LOCATION 1' AS CITY,
'EMPLOYEMENT REGION_CODE 1' AS REGION_CODE,
'EMPLOYEMENT FORMATTED 1' AS FORMATTED
) AS LOCATION
) AS EMPLOYEMENT

) AS DETAILS
)
, TABLE2 AS 
(
SELECT 
'WEBSITE 1' AS WEBSITE,
STRUCT (
STRUCT ('NAME 2') AS NAME,
STRUCT ('AGE 2') AS AGE,
'GENDER' AS GENDER,
STRUCT(
'EMPLOYEMENT NAME 2' AS NAME,
FALSE AS _CURRENT,
'EMPLOYEMENT TITLE 2' AS TITLE
) AS EMPLOYEMENT

) AS DETAILS
)
,UNIONED AS
(
SELECT  WEBSITE,
DETAILS.NAME AS NAME,
DETAILS.AGE AS AGE,
DETAILS.GENDER AS GENDER,
DETAILS.EMPLOYEMENT.NAME AS EMPLOYEMENT_NAME,
DETAILS.EMPLOYEMENT._CURRENT AS EMPLOYEMENT_CURRENT,
DETAILS.EMPLOYEMENT.TITLE AS EMPLOYEMENT_TITLE,
DETAILS.EMPLOYEMENT.LOCATION.CITY AS EMPLOYEMENT_LOCATION_CITY,
DETAILS.EMPLOYEMENT.LOCATION.REGION_CODE AS EMPLOYEMENT_LOCATION_REGION_CODE,
DETAILS.EMPLOYEMENT.LOCATION.FORMATTED AS EMPLOYEMENT_LOCATION_FORMATTED,
FROM TABLE1 
UNION ALL 
SELECT  WEBSITE,
DETAILS.NAME,
DETAILS.AGE,
DETAILS.GENDER,
DETAILS.EMPLOYEMENT.NAME,
DETAILS.EMPLOYEMENT._CURRENT,
DETAILS.EMPLOYEMENT.TITLE,
NULL ,
NULL,
NULL
FROM TABLE2
)
SELECT 
WEBSITE,
STRUCT (
STRUCT (U.NAME ) AS NAME,
STRUCT (U.AGE) AS AGE,
U.GENDER,
STRUCT (
U.EMPLOYEMENT_NAME AS NAME,
U.EMPLOYEMENT_CURRENT AS _CURRENT,
U.EMPLOYEMENT_TITLE AS TITLE,
STRUCT (
U.EMPLOYEMENT_LOCATION_CITY AS CITY,
U.EMPLOYEMENT_LOCATION_REGION_CODE  AS REGION_COD,
U.EMPLOYEMENT_LOCATION_FORMATTED  AS FORMATTED
) AS LOCATION
) AS EMPLOYMENT
) AS DETAILS
FROM  UNIONED AS U