String array to int64 conversion

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.

Answer

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