CODEWITHSUNDEEP
arrayssplitgoogle-bigquery
KKK
KKK

Reputation: 37

How to remove null value in array for BigQuery?

I have a query about BigQuery, Please see the example below:

SELECT SPLIT(path, '/')[OFFSET(0)] part1,
   SPLIT(path, '/')[OFFSET(1)] part2,
   SPLIT(path, '/')[OFFSET(2)] part3,
   SPLIT(path, '/')[OFFSET(3)] part4,
   ARRAY_LENGTH(SPLIT(path, '/')),
   SPLIT(path, '/') 
FROM (SELECT "/a/b/" path)

As seen below, the result is like this. The question is, how can I remove the part1 and part 4 data, and make the f0_ a 2 but not 4?

Upvotes: 1

Views: 2887

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

Below is for BigQuery Standard SQL

#standardSQL
SELECT ARRAY_LENGTH(arr) arr_length, arr
FROM (
  SELECT 
     ARRAY(SELECT * FROM UNNEST(SPLIT(path, '/')) part WHERE part != '') arr
  FROM `project.dataset.table`
)

You can test above with sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT "/a/b/" path
)
SELECT ARRAY_LENGTH(arr) arr_length, arr
FROM (
  SELECT 
     ARRAY(SELECT * FROM UNNEST(SPLIT(path, '/')) part WHERE part != '') arr
  FROM `project.dataset.table`
)

with result

Row arr_length  arr  
1   2           a    
                b

this is the closets I see to what you expect

Upvotes: 3

Related Questions