เข้าถึงค่าใน ARRAY STRUCT ใน Google Bigquery

Aug 20 2020

ฉันเพิ่งเริ่มค้นหาใน Google BigQuery และกำลังพยายามทำให้ช่อง ARRAY ในข้อความค้นหาของฉันแบนราบเพื่อให้ค่าอาร์เรย์แสดงเป็นผลลัพธ์เดียวในรายการที่คั่นด้วยจุลภาค ในข้อความค้นหาของฉัน "Association.associatedvids" คือช่องอาร์เรย์ในตารางดีล ปัญหาของฉันเป็นปัญหา 2 ขั้นตอนจริง ๆ เนื่องจากฉันต้องจับคู่ Associatedvids กับฟิลด์ชื่อและนามสกุลที่เกี่ยวข้องในตารางอื่นที่เรียกว่าผู้ติดต่อ อันดับแรกสำหรับรหัสผู้ติดต่อเมื่อฉันทำสิ่งต่อไปนี้

Select
CAST(property_hs_object_id.value AS String) AS deal_ID,
associations.associatedvids AS associated_contacts_ID
From hubspot_data.deals

ฉันได้รับผลลัพธ์ดังนี้:

Row    deal_ID         associated_contacts_ID.value 
1      1814103617      3240001
                       3239951
...

แต่สิ่งที่ฉันต้องการคือ:

Row    deal_ID         associated_contacts_ID.value 
1      1814103617      3240001,3239951
...

ฉันได้ลองทดสอบอาร์เรย์หลายวิธีแล้ว แต่ดูเหมือนจะไม่ถูกต้อง ตัวอย่างเช่นความพยายามต่อไปนี้ส่งกลับข้อผิดพลาด "ข้อความค้นหาย่อย Scalar สร้างมากกว่าหนึ่งองค์ประกอบ"

Select
CAST(property_hs_object_id.value AS String) AS deal_ID,
(select associations.associatedvids from unnest(associations.associatedvids)) AS associated_contacts_ID
From hubspot_data.deals

ประการที่สองสิ่งที่ฉันต้องการในที่สุดคือ:

Row    deal_ID         associated_contact_names 
1      1814103617      John Doe,Jane Doe
...

ฟิลด์ชื่อคือ property_firstname.value และ property_lastname.value และ Association.associatedvids (ชนิดข้อมูล ARRAY <STRUCT>) = contacts.vids (ชนิดข้อมูล INT64) ฉันเหนื่อยกับสิ่งต่อไปนี้ แต่เนื่องจากประเภทข้อมูลแตกต่างกันฉันจึงได้รับข้อผิดพลาด

Select
CAST(property_hs_object_id.value AS String) AS deal_ID,
(select concat(property_firstname.value, " ", property_lastname.value)
 from hubspot_data.contacts
 where contacts.vid=associations.associatedvids) AS contact_name
From hubspot_data.deals

คำแนะนำใด ๆ จะได้รับการชื่นชมมาก!

แก้ไข: นี่คือความพยายามของฉันในตัวอย่างโค้ดที่ใช้งานได้น้อยที่สุด ฉันเชื่อว่าฟิลด์ที่ฉันพยายามค้นหาคือ ARRAY of STURCT ที่มีชนิดข้อมูลขององค์ประกอบโครงสร้างที่ฉันต้องการให้เป็น INT64

WITH deals AS (
  Select "012345" as deal_ID,
    [STRUCT(["abc"] as company_ID, [123,678,810] as contact_ID)]
      AS associations)
SELECT 
  deal_ID,
  contacts
FROM deals d
CROSS JOIN UNNEST(d.associations) as contacts

สิ่งนี้ให้ฉัน:

Row    deal_ID    contacts.company_ID    contacts.contact_ID    
1      012345     abc                    123
                                         678
                                         810

แต่สิ่งที่ฉันต้องการคือ

Row    deal_ID    contacts.contact_ID   
1      012345     123, 678, 810

และท้ายที่สุดฉันต้องแทนที่ contact_IDs ด้วยชื่อและนามสกุลของผู้ติดต่อที่อยู่ในตารางอื่น (แต่โชคดีที่ไม่อยู่ในอาร์เรย์)

คำตอบ

1 MikhailBerlyant Aug 20 2020 at 04:46

ด้านล่างนี้ใช้สำหรับ BigQuery Standard SQL

จากข้อมูลที่ จำกัด ในคำถามของคุณ - ฉันเดาว่าคุณไม่มี STRING_AGG ในคำถามที่สองที่คุณนำเสนอในคำถามของคุณ

มันควรจะเป็น

SELECT
  CAST(property_hs_object_id.value AS String) AS deal_ID,
  (SELECT STRING_AGG(associations.associatedvids) FROM UNNEST(associations.associatedvids)) AS associated_contacts_ID
FROM hubspot_data.deals   

อัปเดต: ตอบคำถามที่อัปเดต

#standardSQL
SELECT 
  deal_ID,
  ARRAY(
    SELECT AS STRUCT 
      company_ID, 
      ( SELECT STRING_AGG(CAST(id AS STRING), ', ') 
        FROM t.contact_ID id
      ) AS contact_ID 
    FROM d.associations t
  ) AS contacts
FROM deals d