Create an array with NULL values/0 and find array length excluding null/0

Question

I want to find the number of columns in a range in each row which has non-null and >0 value.

I have done this currently using case when statements or IF-ELSE. But the number of columns that i have to now consider has increased and with that the number of case statements too.

So i wanted to create an array of the columns and then find the length of the array after excluding 0 and NULL values.

I tried the follow code but i am getting an error

**case1**
SELECT [col1,col2,col3,col4,col5] from input_Table

Error: Array cannot have a null element; error in writing field

**case2**
SELECT *,
  ARRAY(SELECT col1,col2,col3,col4,col5
        from input_table
        WHERE col1 is not null and col2 is not null ...) 
from input_Table

Error: ARRAY subquery cannot have more than one column unless using SELECT AS STRUCT to build STRUCT values at [2:3]

Below is a snapshot of my data

The output that i want is

1
2
0

It would be super helpful if somebody can help me with this, I am very new to Bigquery.

Mikhail Berlyant · Accepted Answer · 2019-01-07 05:32:14Z

I want to find the number of columns in a range in each row which has non-null and >0 value ...

Option 1

Below is for BigQuery and generic enough to work for any number of columns

SELECT *,
  (SELECT COUNT(1) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(
        TO_JSON_STRING(t), r'"col\d+":(.*?)[,}]')     
      ) value
    WHERE NOT value IN ('null', '0')
  ) AS non_null_0_count
FROM `project.dataset.table` t

Above assumes pattern for columns as col1, col2, .., colNN

You can test , play with above using dummy data from your question as in below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 col1, 0 col2, 0 col3, 0 col4, 0 col5 UNION ALL
  SELECT 2, 4, 5, 0, 0, 0 UNION ALL
  SELECT 3, NULL, NULL, NULL, NULL, NULL
)
SELECT *,
  (SELECT COUNT(1) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(
        TO_JSON_STRING(t), r'"col\d+":(.*?)[,}]')     
      ) value
    WHERE NOT value IN ('null', '0')
  ) AS non_null_0_count
FROM `project.dataset.table` t

with result

Row id  col1    col2    col3    col4    col5    non_null_0_count     
1   1   1       0       0       0       0       1    
2   2   4       5       0       0       0       2    
3   3   null    null    null    null    null    0

Option 2

In case if above mentioned column pattern is not really a case - this approach still works - see example below - you just need to enumerate those columns within regexp

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 abc, 0 xyz, 0 qwe, 0 asd, 0 zxc UNION ALL
  SELECT 2, 4, 5, 0, 0, 0 UNION ALL
  SELECT 3, NULL, NULL, NULL, NULL, NULL
)
SELECT *,
  (SELECT COUNT(1) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(
        TO_JSON_STRING(t), r'"(?:abc|xyz|qwe|asd|zxc)":(.*?)[,}]')
      ) value
    WHERE NOT value IN ('null', '0') 
  ) AS non_null_0_count
FROM `project.dataset.table` t

with result as

Row id  abc     xyz     qwe     asd     zxc     non_null_0_count     
1   1   1       0       0       0       0       1    
2   2   4       5       0       0       0       2    
3   3   null    null    null    null    null    0

Option 3

Obviously, the most simple and straightforward option is

#standardSQL
SELECT *, 
  (
    SELECT COUNT(1)
    FROM (
      SELECT col1 col UNION ALL 
      SELECT col2 UNION ALL 
      SELECT col3 UNION ALL 
      SELECT col4 UNION ALL 
      SELECT col5
    )
    WHERE NOT col IS NULL AND col != 0
  ) AS non_null_0_count
FROM `project.dataset.table` t

Gordon Linoff · Accepted Answer · 2019-01-07 02:20:21Z

2

One method is to simply use case -- because you know the number of columns:

select id,
       (case when col1 = 0 or col1 is null then 0
             when col2 = 0 or col2 is null then 1
             when col3 = 0 or col3 is null then 2
             when col4 = 0 or col4 is null then 3
             when col5 = 0 or col5 is null then 4
             else 5
        end) as result             
from t;

Although can do fancy manipulations with arrays, I don't see a need for this, given that the number of columns is finite and the case expression is pretty simple.

answered Jan 7, 2019 at 2:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Create an array with NULL values/0 and find array length excluding null/0

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related