I've two tables and I need to join them.
But the common field are of different data types.
Table A - ID field is of string array and Table B ID is of Int64.
Tried to cast String array to Int 64 getting error "Invalid cast from ARRAY to INT64"
Is there anyway I can convert and join the tables.
So, from what I understand -> you are trying to cast an ARRAY<string> datatype to INT64 which is not possible
Error:
Invalid cast from ARRAY<STRING> to INT64 at [4:17]
Instead, you gotta unnest the STRING ARRAY and cast the unnested flat STRING to INT64 and then join with table B
SELECT
A.id as a_id, A.flat_str, A.flat_int, B.id as b_id, B.int_val as b_int_val
FROM (
SELECT
tableA.id,
flat_str,
CAST(flat_str AS INT64) AS flat_int
FROM
tableA,
UNNEST(tableA.str_arr) flat_str ) AS A
LEFT OUTER JOIN
tableB AS B
ON
A.id = B.id
AND A.flat_int = B.int_val
Complete Query Simulation:
WITH
tableA AS (
SELECT
1 AS id,
['1',
'2',
'3',
'4',
'5'] AS str_arr ),
tableB AS (
SELECT
1 AS id,
1 AS int_val
UNION ALL
SELECT
1 AS id,
2 AS int_val
UNION ALL
SELECT
1 AS id,
3 AS int_val
UNION ALL
SELECT
1 AS id,
4 AS int_val
UNION ALL
SELECT
1 AS id,
6 AS int_val )
SELECT
A.id as a_id, A.flat_str, A.flat_int, B.id as b_id, B.int_val as b_int_val
FROM (
SELECT
tableA.id,
flat_str,
CAST(flat_str AS INT64) AS flat_int
FROM
tableA,
UNNEST(tableA.str_arr) flat_str ) AS A
LEFT OUTER JOIN
tableB AS B
ON
A.id = B.id
AND A.flat_int = B.int_val
Sample Result
