acessando valores em um ARRAY STRUCT no Google Bigquery

Question

Sou novo em consultas no Google BigQuery e estou tentando nivelar um campo ARRAY em minha consulta para que os valores da matriz sejam listados como um único resultado em uma lista separada por vírgulas. Na minha consulta, "associações.associatedvids" é um campo de matriz na tabela de negócios. Meu problema é realmente um problema de 2 etapas, pois também preciso combinar os vídeos associados com os campos de nome e sobrenome correspondentes em outra tabela chamada contatos. Primeiro, para os IDs de contato, quando faço o seguinte

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

Eu obtenho um resultado como este:

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

mas o que eu quero é:

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

Eu tentei diferentes maneiras de desaninhar a matriz, mas não consigo acertar. Por exemplo, a seguinte tentativa retorna o erro "Subconsulta escalar produziu mais de um elemento".

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

Em segundo lugar, o que eu quero no final das contas é:

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

Os campos de nomes são property_firstname.value e property_lastname.value, e transactions.associatedvids (tipo de dados ARRAY <STRUCT>) = contacts.vids (tipo de dados INT64). Eu cansei do seguinte, mas como os tipos de dados são diferentes, estou recebendo um erro.

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

Qualquer orientação seria muito apreciada!

EDIT: Aqui está minha tentativa de um exemplo de código mínimo de trabalho. Acredito que o campo que estou tentando consultar é um ARRAY de STURCTs com o tipo de dados do elemento Struct que desejo 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

isso me dá:

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

mas o que eu quero é

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

E, finalmente, preciso substituir o contact_IDs pelo nome e sobrenome do contato que estão em uma tabela diferente (mas felizmente não em uma matriz).

Answer 1

Abaixo está o BigQuery Standard SQL

Com base nas informações limitadas da sua pergunta - acho que você está perdendo STRING_AGG na segunda consulta que apresentou na sua pergunta

Deveria ser

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   

Atualização: resposta à pergunta atualizada

#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