Zugriff auf Werte in einem ARRAY STRUCT in Google Bigquery

Question

Ich bin neu in der Abfrage in Google BigQuery und versuche, ein ARRAY-Feld in meiner Abfrage zu reduzieren, sodass die Array-Werte als einzelnes Ergebnis in einer durch Kommas getrennten Liste aufgeführt werden. In meiner Abfrage ist "Associations.associatedvids" ein Array-Feld in der Deals-Tabelle. Mein Problem ist wirklich ein 2-Schritt-Problem, da ich auch die zugehörigen Videos mit den entsprechenden Vor- und Nachnamenfeldern in einer anderen Tabelle namens Kontakte abgleichen muss. Zunächst für die Kontakt-IDs, wenn ich Folgendes tue

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

Ich bekomme ein Ergebnis wie dieses:

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

aber was ich will ist:

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

Ich habe verschiedene Methoden ausprobiert, um das Array zu entfernen, kann es aber nicht richtig machen. Der folgende Versuch gibt beispielsweise den Fehler "Skalare Unterabfrage hat mehr als ein Element erzeugt" zurück.

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

Zweitens möchte ich letztendlich:

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

Die Namensfelder sind property_firstname.value und property_lastname.value sowie Associations.associatedvids (Datentyp ARRAY <STRUCT>) = contacts.vids (Datentyp INT64). Ich habe Folgendes müde gemacht, aber da die Datentypen unterschiedlich sind, wird eine Fehlermeldung angezeigt.

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

Jede Anleitung wäre sehr dankbar!

EDIT: Hier ist mein Versuch, ein minimal funktionierendes Beispiel für Code zu erstellen. Ich glaube, das Feld, das ich abfragen möchte, ist ein ARRAY von STURCTs mit dem Datentyp des Struct-Elements, das ich INT64 sein möchte.

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

das gib mir:

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

aber was ich will ist

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

Und letztendlich muss ich die contact_IDs durch die Vor- und Nachnamen des Kontakts ersetzen, die sich in einer anderen Tabelle befinden (aber zum Glück nicht in einem Array).

Answer 1

Unten finden Sie Informationen zu BigQuery Standard SQL

Basierend auf begrenzten Informationen in Ihrer Frage - Ich denke, Sie vermissen STRING_AGG in der zweiten Abfrage, die Sie in Ihrer Frage präsentiert haben

Es sollte sein

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   

Update: Antwort auf aktualisierte Frage

#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