доступ к значениям в ARRAY STRUCT в Google Bigquery

Aug 20 2020

Я новичок в запросах в Google BigQuery, и я пытаюсь сгладить поле ARRAY в своем запросе, чтобы значения массива указывались как один результат в списке, разделенном запятыми. В моем запросе «association.associatedvids» - это поле массива в таблице сделок. Моя проблема на самом деле представляет собой двухэтапную проблему, так как мне также нужно сопоставить связанные идентификаторы с соответствующими полями имени и фамилии в другой таблице, называемой контактами. Во-первых, для идентификаторов контактов, когда я делаю следующее

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
...

Полями имен являются имя_свойства.значение и имя_свойства.значение и ассоциации.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

Любое руководство будет очень признательно!

РЕДАКТИРОВАТЬ: Вот моя попытка минимального рабочего примера фрагмента кода. Я считаю, что поле, которое я пытаюсь запросить, является Массивом STURCT с типом данных элемента Struct, который я хочу быть 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

это дайте мне:

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 на имя и фамилию контакта, которые находятся в другой таблице (но, к счастью, не в массиве).

Ответы

1 MikhailBerlyant Aug 20 2020 at 04:46

Ниже приведен стандартный SQL BigQuery.

Основываясь на ограниченной информации в вашем вопросе - я думаю, вам не хватает 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