BigQuery表连接报错：INT64与ARRAY<INT64>类型不匹配求解

Hey there! Let's sort out this join issue you're facing—coming from Postgres, BigQuery's array handling has a few syntax quirks that can trip you up at first. The error you're seeing makes total sense: you can't directly compare a scalar INT64 value to an ARRAY<INT64> using =; BigQuery needs explicit logic to check if the scalar exists within the array.

Here are two solid solutions depending on your use case:

1. Unnest the Array (for row-level matching) #

If you need to create a separate row for every element in c.vid that matches d.associations.associatedvids, use UNNEST to expand the array into individual scalar values:

SELECT *
FROM your_table_c c
CROSS JOIN UNNEST(c.vid) AS unnested_vid
JOIN your_table_d d
  ON unnested_vid = d.associations.associatedvids

This works similarly to unnesting arrays in Postgres, but BigQuery requires explicitly joining the unnested values with CROSS JOIN UNNEST. Each element in c.vid becomes its own row, so you'll get one row per matching array element.

2. Check Array Membership (without unnesting) #

If you don't want to split rows and just need to verify that d.associations.associatedvids exists inside c.vid, use either the IN operator with UNNEST or the built-in ARRAY_CONTAINS function:

Option A: Using IN UNNEST() #

SELECT *
FROM your_table_c c
JOIN your_table_d d
  ON d.associations.associatedvids IN UNNEST(c.vid)

This is the BigQuery equivalent of Postgres' = ANY(c.vid) syntax, adapted to the platform's requirements.

Option B: Using ARRAY_CONTAINS() #

SELECT *
FROM your_table_c c
JOIN your_table_d d
  ON ARRAY_CONTAINS(c.vid, d.associations.associatedvids)

This is a clean, BigQuery-native function that explicitly checks if the scalar value is present in the array. It's perfect for straightforward membership checks without row splitting.

Just pick the approach that aligns with what you need from your join results—both will resolve the type mismatch error you're seeing.

内容的提问来源于stack exchange，提问作者Tajs