Google Bigquery에서 ARRAY STRUCT의 값에 액세스

Aug 20 2020

Google BigQuery에서 쿼리를 처음 사용하며 배열 값이 쉼표로 구분 된 목록에 단일 결과로 나열되도록 쿼리에서 ARRAY 필드를 평면화하려고합니다. 내 쿼리에서 "associations.relatedvids"는 deals 테이블의 배열 필드입니다. 내 문제는 연락처라는 다른 테이블의 해당 이름 및 성 필드와 associatedvids를 일치시켜야하기 때문에 실제로 2 단계 문제입니다. 먼저 연락처 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
...

배열의 중첩을 해제하는 다른 방법을 시도했지만 제대로 된 것 같지 않습니다. 예를 들어 다음 시도는 "Scalar 하위 쿼리가 둘 이상의 요소를 생성했습니다"오류를 반환합니다.

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.relatedvids (데이터 유형 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

어떤 지침이라도 대단히 감사하겠습니다!

편집 : 여기에 최소한의 작업 예제 코드에 대한 나의 시도가 있습니다. 쿼리하려는 필드는 내가 원하는 Struct 요소의 데이터 유형이 INT64 인 STURCT의 ARRAY라고 생각합니다.

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

다음은 BigQuery 표준 SQL 용입니다.

질문의 제한된 정보를 기반으로합니다. 질문에 제시 한 두 번째 쿼리에서 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