GoogleBigqueryのARRAYSTRUCTの値にアクセスする

Question

Google BigQueryでクエリを実行するのは初めてで、クエリのARRAYフィールドをフラット化して、配列値が単一の結果としてコンマ区切りのリストに表示されるようにしようとしています。私のクエリでは、「associations.associatedvids」は取引テーブルの配列フィールドです。私の問題は実際には2段階の問題です。これは、associatedvidsをcontactsと呼ばれる別のテーブルの対応する姓名フィールドと照合する必要があるためです。まず、連絡先IDについて、次のことを行うと

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、およびassociations.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にしたいStruct要素のデータ型を持つSTURCTの配列だと思います。

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_IDを、別のテーブルにある（ただし、幸い配列にはない）連絡先の姓名に置き換える必要があります。

Answer 1

以下はBigQuery標準SQL用です

質問の限られた情報に基づく-質問で提示した2番目のクエリで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