mengakses nilai-nilai dalam ARRAY STRUCT di Google Bigquery

Question

Saya baru dalam membuat kueri di Google BigQuery dan sedang mencoba meratakan bidang ARRAY di kueri saya sehingga nilai larik dicantumkan sebagai satu hasil dalam daftar yang dipisahkan koma. Dalam kueri saya, "Associations.associatedvids" adalah kolom array di tabel transaksi. Masalah saya sebenarnya adalah masalah 2 langkah karena saya juga harus mencocokkan data terkait dengan bidang nama depan dan belakang yang sesuai di tabel lain yang disebut kontak. Pertama, untuk nomor kontak, saat saya melakukan hal berikut

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

Saya mendapatkan hasil seperti ini:

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

tapi yang saya inginkan adalah:

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

Saya telah mencoba berbagai cara untuk tidak menguji array, tetapi sepertinya tidak bisa melakukannya dengan benar. Misalnya, upaya berikut mengembalikan kesalahan "subkueri skalar menghasilkan lebih dari satu elemen".

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

Kedua, yang pada akhirnya saya inginkan adalah:

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

Bidang nama adalah property_firstname.value dan property_lastname.value, dan associate.associatedvids (tipe data ARRAY <STRUCT>) = contacts.vids (tipe data INT64). Saya sudah lelah dengan yang berikut ini, tetapi karena tipe datanya berbeda, saya mendapatkan kesalahan.

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

Panduan apa pun akan sangat dihargai!

EDIT: Ini adalah upaya saya pada potongan contoh kode yang berfungsi minimal. Saya percaya bidang yang saya coba tanyakan adalah ARRAY STURCTs dengan tipe data elemen Struct yang saya inginkan adalah 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

ini memberi saya:

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

tapi yang kuinginkan adalah

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

Dan akhirnya, saya perlu mengganti contact_IDs dengan nama depan dan belakang kontak yang ada di tabel berbeda (tapi untungnya tidak dalam array).

Answer 1

Berikut ini untuk BigQuery Standard SQL

Berdasarkan info terbatas dalam pertanyaan Anda - Saya rasa Anda kehilangan STRING_AGG pada kueri kedua yang Anda berikan pada pertanyaan Anda

Harus

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   

Pembaruan: jawaban atas pertanyaan yang diperbarui

#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