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

Question

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.

Sergey Geron · Accepted Answer · 2021-07-13 03:30:07Z

3

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_

edited Jul 13, 2021 at 3:30

answered Jul 13, 2021 at 3:01

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mikhail Berlyant Over a year ago

isn't it going to generate same exact error?!

Sergey Geron Over a year ago

Oh, I see it now. Thank you @MikhailBerlyant.

lipo Over a year ago

Thank you very much I really appreciate this. People kept closing my question so I really appreciate this :)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related