Big Query SQL check if an array is null otherwise return 0

user_14574832 0
Viewed 796

In big query, I am trying to show the array only if the array is not empty and else we return 0. The field "url" is an array with a type of string, however I am having difficulty implementing the logic. Side note I will not be able to use a where filter to filter the data but rather keep the original data and just perform a check on the field and have a placeholder of 0 is it's empty.

WITH temp_ as(
SELECT
    ASE_IDD,
    REGEXP_EXTRACT_ALL(
      DESCRIPTION,
      r"regex_placeholder") as url
     
  FROM
    table)
   
  SELECT ASE_IDD, IF ( (url is not NULL), url, 0) from temp_

right not it does not work even though it should any help would be appreciated :)

here is my error:

1::SQL_ANALYSIS_ERROR: No matching signature for function IF for argument types: BO

I know what the error means but I don't what things I could do to solve it. I know my url is an array but is there any other way of comparison that will not use if or nullif or is there any other function that could help me solve this? SO please don't close my question I'm just looking for extra information I might have missed.

google-bigquery
1 Answers

All values of the single column of the returned results should be of the same type. Therefore we cannot return both ARRAY-type (url array) and INT-type (digit 0) in the same column. We can convert both to STRING-type instead. Try array_length and to_json_string:

SELECT ASE_IDD, IF(array_length(url) > 0, to_json_string(url), "0") from temp_
user_134735250
Related