Google Bigquery में ARRAY STRUCT में मान प्राप्त करना

Question

मैं Google BigQuery में क्वेरी करने के लिए नया हूं और अपनी क्वेरी में एक ARRAY फ़ील्ड को समतल करने का प्रयास कर रहा हूं ताकि सरणी मान अल्पविराम से अलग की गई सूची में एकल परिणाम के रूप में सूचीबद्ध हों। मेरी क्वेरी में "Associates.associatedvids" डील टेबल में एक सरणी फ़ील्ड है। मेरा मुद्दा वास्तव में एक 2 कदम की समस्या है क्योंकि मुझे संपर्क नाम की एक अन्य तालिका में संबंधित प्रथम और अंतिम नाम फ़ील्ड के साथ संबद्धता का मिलान करने की भी आवश्यकता है। संपर्क आईडी के लिए सबसे पहले, जब मैं निम्न कार्य करता हूं

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
...

मैंने सरणी को बेकार करने के विभिन्न तरीकों की कोशिश की है, लेकिन यह सही नहीं लगता है। उदाहरण के लिए, निम्न प्रयास "स्केलर उपकुंजी एक से अधिक तत्वों का उत्पादन" त्रुटि देता है।

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, और Associates.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

किसी भी मार्गदर्शन की बहुत सराहना की जाएगी!

संपादित करें: यहाँ कोड के न्यूनतम काम करने के उदाहरण पर मेरा प्रयास है। मेरा मानना ​​है कि मैं जिस क्षेत्र में क्वेरी करने की कोशिश कर रहा हूं, वह डेटा तत्व प्रकार है जिसमें मैं चाहता हूं कि मैं 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 को पहले और अंतिम नामों के संपर्क से बदलना होगा जो एक अलग तालिका में हैं (लेकिन सौभाग्य से एक सरणी में नहीं)।

Answer 1

नीचे BigQuery मानक 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