acceder a valores en una ESTRUCTURA ARRAY en Google Bigquery

Aug 20 2020

Soy nuevo en la consulta en Google BigQuery y estoy intentando aplanar un campo ARRAY en mi consulta para que los valores de la matriz se enumeren como un único resultado en una lista separada por comas. En mi consulta, "association.associatedvids" es un campo de matriz en la tabla de ofertas. Mi problema es realmente un problema de 2 pasos, ya que también necesito hacer coincidir los elementos asociados con los campos de nombre y apellido correspondientes en otra tabla llamada contactos. Primero, para los ID de contacto, cuando hago lo siguiente

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

Obtengo un resultado como este:

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

pero lo que quiero es:

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

He intentado diferentes formas de desanidar la matriz, pero parece que no puedo hacerlo bien. Por ejemplo, el siguiente intento devuelve el error "La subconsulta escalar produjo más de 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

En segundo lugar, lo que quiero en última instancia es:

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

Los campos de nombres son property_firstname.value y property_lastname.value, y association.associatedvids (tipo de datos ARRAY <STRUCT>) = contactos.vids (tipo de datos INT64). Me cansé de lo siguiente, pero como los tipos de datos son diferentes, recibo un error.

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

¡Cualquier orientación sería muy apreciada!

EDITAR: Aquí está mi intento de un ejemplo de código mínimo funcional. Creo que el campo que estoy tratando de consultar es un ARRAY de STURCT con el tipo de datos del elemento Struct que quiero que sea 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

esto me da:

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

pero lo que quiero es

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

Y, en última instancia, necesito reemplazar los contact_IDs con el nombre y apellido del contacto que están en una tabla diferente (pero afortunadamente no en una matriz).

Respuestas

1 MikhailBerlyant Aug 20 2020 at 04:46

A continuación se muestra para SQL estándar de BigQuery

Según la información limitada de su pregunta, supongo que le falta STRING_AGG en la segunda consulta que presentó en su pregunta

Debería 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   

Actualización: respuesta a la pregunta actualizada

#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