如何解决 STRUCT问题此查询有错误吗？

Question

我正在尝试从不同的表中获取“视频 ID/项目代码”并将它们全部添加到我的基表中。所有视频 ID 都有一个我试图通过交叉连接获得的品牌名称。我进行了以下查询，但收到错误（参数类型：DATE、STRUCT 的运算符 || 没有匹配的签名。支持的签名：STRING || STRING; BYTES || BYTES; ARRAY || ARRAY on my key 错误'code')，有谁知道如何让这个查询工作？我的主要目标是在一张表中获取所有“视频 ID/项目代码”，以便稍后获取元数据。我将不胜感激任何帮助。

WITH
base AS
    (
        SELECT
            LOWER(date || project_code || k.parentbreand ) key,
            date,
            project_code asset,
            k.parentbreand brand,
        FROM
            UNNEST((SELECT GENERATE_DATE_ARRAY(${when(incremental(),`${constants.ads_starting_date}`, `${constants.last_fy_start}`)}, ${constants.ads_ending_date}))) AS date  
            CROSS JOIN (SELECT DISTINCT ParentBrand FROM ${ref("master_brand_key_views")} UNION ALL
             SELECT DISTINCT Parent_Brand FROM ${ref("master_brand_parent_list")} WHERE (New_Vertical = '*' AND Parent_Brand <> '*') 
                OR LOWER(Parent_Brand) in ('*','*','*','*','*','*','*','*')) k

            CROSS JOIN ((SELECT COALESCE(REGEXP_EXTRACT(upper(title), r'([A-Za-z0-9]+)SC\b'),
            regexp_extract(upper(title),r'\d{6,7}?'), 
            regexp_extract(upper(title),r'\b[A-Za-z0-9]{6,7}\b')) FROM ${ref({database: "datamart-internal", schema: "video", name: "snapchat"})} WHERE COALESCE(REGEXP_EXTRACT(upper(title), r'([A-Za-z0-9]+)SC\b'),
            regexp_extract(upper(title),r'\d{6,7}?'), 
            regexp_extract(upper(title),r'\b[A-Za-z0-9]{6,7}\b')) is not null) 
            UNION ALL (Select universal_video_id FROM ${ref({database: "datamart-internal", schema: "video", name: "facebook"})} where universal_video_id is not NULL)

            UNION ALL (Select project_code FROM ${ref({database: "datamart-internal", schema: "video", name: "youtube_detailed"})} where project_code is not NULL)

            UNION ALL (Select project_code FROM ${ref({database: "datamart-internal", schema: "video", name: "jwplayer_performance"})} where project_code is not NULL)) as project_code 
    
     
         LEFT JOIN ${ref("master_brand_parent_list")} p ON k.ParentBrand = p.Parent_Brand 
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "snapchat"})} s ON k.ParentBrand = s.brand 
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "facebook"})} f ON k.ParentBrand = f.brand 
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "youtube_detailed"})} y ON k.ParentBrand = y.parent_brand
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "jwplayer_performance"})} j ON k.ParentBrand = j.brand 

    ),

Answer 1

我进行了这些更改，并奏效了：看来它无法正确阅读大交叉加入。

select
LOWER(ARRAY_TO_STRING([CAST(date AS STRING),project_code.project_code,k.parentbrand],"")) key,
 date,
 project_code.project_code,
 k.parentbrand brand,

from(the rest)