accedere ai valori in un ARRAY STRUCT in Google Bigquery

Aug 20 2020

Sono nuovo nell'esecuzione di query in Google BigQuery e sto tentando di appiattire un campo ARRAY nella mia query in modo che i valori dell'array siano elencati come un unico risultato in un elenco separato da virgole. Nella mia query "associazioni.associatedvids" è un campo array nella tabella delle offerte. Il mio problema è davvero un problema in 2 passaggi poiché ho anche bisogno di abbinare i video associati con i campi del nome e del cognome corrispondenti in un'altra tabella chiamata contatti. Innanzitutto, per gli ID contatto, quando eseguo le seguenti operazioni

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

Ottengo un risultato come questo:

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

ma quello che voglio è:

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

Ho provato diversi modi per innestare l'array, ma non riesco a farlo bene. Ad esempio, il seguente tentativo restituisce l'errore "La sottoquery scalare ha prodotto più di un 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

In secondo luogo, quello che alla fine voglio è:

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

I campi dei nomi sono property_firstname.value e property_lastname.value e associazioni.associatedvids (tipo di dati ARRAY <STRUCT>) = contacts.vids (tipo di dati INT64). Mi sono stancato di quanto segue, ma poiché i tipi di dati sono diversi, ricevo un errore.

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

Qualsiasi guida sarebbe molto apprezzata!

EDIT: Ecco il mio tentativo di un pezzo di codice di esempio funzionante minimo. Credo che il campo che sto cercando di interrogare sia un ARRAY di STURCT con il tipo di dati dell'elemento Struct che desidero essere 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

questo dammi:

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

ma quello che voglio è

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

E alla fine, devo sostituire contact_IDs con il nome e il cognome del contatto che si trovano in una tabella diversa (ma fortunatamente non in un array).

Risposte

1 MikhailBerlyant Aug 20 2020 at 04:46

Di seguito è riportato per BigQuery Standard SQL

Sulla base delle informazioni limitate nella tua domanda, immagino che manchi STRING_AGG nella seconda query che hai presentato nella tua domanda

Dovrebbe essere

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   

Aggiornamento: risposta alla domanda aggiornata

#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