uzyskiwanie dostępu do wartości w ARRAY STRUCT w Google Bigquery

Question

Nie mam doświadczenia w wykonywaniu zapytań w Google BigQuery i próbuję spłaszczyć pole ARRAY w moim zapytaniu, aby wartości tablic były wymienione jako pojedynczy wynik na liście oddzielonej przecinkami. W moim zapytaniu „Association.associatedvids” jest polem tablicy w tabeli transakcji. Mój problem jest tak naprawdę problemem dwuetapowym, ponieważ muszę również dopasować powiązane vids z odpowiednimi polami imienia i nazwiska w innej tabeli o nazwie kontakty. Po pierwsze, dla identyfikatorów kontaktów, kiedy wykonuję następujące czynności

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

Otrzymuję taki wynik:

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

ale ja chcę:

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

Próbowałem różnych sposobów rozpinania tablicy, ale wydaje się, że nie mogę tego zrobić dobrze. Na przykład następująca próba zwraca błąd „Podzapytanie skalarne wygenerowało więcej niż jeden element”.

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

Po drugie, ostatecznie chcę:

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

Pola nazw to property_firstname.value i property_lastname.value, oraz Associations.associatedvids (typ danych ARRAY <STRUCT>) = contacts.vids (typ danych INT64). Zmęczyłem następujące kwestie, ale ponieważ typy danych są różne, pojawia się błąd.

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

Wszelkie wskazówki byłyby bardzo mile widziane!

EDYCJA: Oto moja próba stworzenia minimalnego działającego fragmentu kodu. Wydaje mi się, że pole, które próbuję odpytać, to ARRAY STURCT z typem danych elementu Struct, który chcę, aby był 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

to daj mi:

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

ale ja chcę

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

Ostatecznie muszę zastąpić contact_ID imionami i nazwiskami kontaktów, które znajdują się w innej tabeli (ale na szczęście nie w tablicy).

Answer 1

Poniżej znajduje się opis BigQuery Standard SQL

Na podstawie ograniczonych informacji w Twoim pytaniu - wydaje mi się, że brakuje Ci STRING_AGG w drugim zapytaniu przedstawionym w pytaniu

Powinno być

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   

Aktualizacja: odpowiedź na zaktualizowane pytanie

#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