accéder aux valeurs d'une ARRAY STRUCT dans Google Bigquery

Question

Je suis nouveau dans les requêtes dans Google BigQuery et j'essaie d'aplatir un champ ARRAY dans ma requête afin que les valeurs du tableau soient répertoriées comme un résultat unique dans une liste séparée par des virgules. Dans ma requête "associations.associatedvids" est un champ de tableau dans la table des offres. Mon problème est vraiment un problème en 2 étapes car je dois également faire correspondre les associésvids avec les champs de prénom et de nom correspondants dans une autre table appelée contacts. Tout d'abord, pour les identifiants de contact, lorsque je fais ce qui suit

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

J'obtiens un résultat comme celui-ci:

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

mais ce que je veux c'est:

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

J'ai essayé différentes façons de désengorger le tableau, mais je n'arrive pas à faire les choses correctement. Par exemple, la tentative suivante renvoie l'erreur "La sous-requête scalaire a produit plus d'un élément".

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

Deuxièmement, ce que je veux en fin de compte, c'est:

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

Les champs de noms sont property_firstname.value et property_lastname.value, et associations.associatedvids (type de données ARRAY <STRUCT>) = contacts.vids (type de données INT64). J'ai fatigué ce qui suit, mais comme les types de données sont différents, j'obtiens une erreur.

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

Toute orientation serait très appréciée!

EDIT: Voici ma tentative d'un exemple de code de travail minimal. Je crois que le champ que j'essaie d'interroger est un ARRAY de STURCTs avec le type de données de l'élément Struct que je veux être 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

cela me donne:

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

mais ce que je veux c'est

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

Et finalement, je dois remplacer les contact_IDs par les noms et prénoms des contacts qui se trouvent dans une table différente (mais heureusement pas dans un tableau).

Answer 1

Ci-dessous, pour BigQuery Standard SQL

Sur la base d'informations limitées dans votre question - je suppose que STRING_AGG vous manque dans la deuxième requête que vous avez présentée dans votre question

Ça devrait être

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   

Mise à jour: réponse à la question mise à jour

#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