Bigquery using case when for different datatypes in the same column

Question

I have column that has both integers and timestamp in a single column. I am trying to check with case when and if the datatype is integer I want to use Timestamp_seconds to cast it if not I want to use the default value.

SELCT
  CASE WHEN  CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64) IS NOT NULL 
       THEN TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64))  
  END AS job_created_timestamp FROM tableA ;

is giving Bad int64 value: 2020-10-28T02:15:35.666Z for values that are not integers. And I tried many options like IS TRUE ,ELSE for case when and nothing seems to work. Please let me know for any thoughts. Thanks.

Answer 1

The workaround is this :

Use case statement to separate out Int64 and timestamp values of a column in a With clause(as separate columns) and then merge them in the select statement using COALESCE(temp.integer_timestamp,temp.timestamp,NULL)