BigQuery: Array index 1 is out of bounds (overflow) [Finding distinct counts for ALL columns without specifying column names)

Question

On Bigquery, I have a table with many columns. I want to create a new table showing counts of all distinct values for all columns.

I am using the below code:

SELECT key AS column_name,
COUNT(DISTINCT val) AS number_of_options,
ARRAY_AGG(DISTINCT val) AS distinct_options
FROM (
  SELECT TRIM(x[0], '"') AS key, 
  TRIM(x[1], '"') AS val
  FROM t,
  UNNEST(SPLIT(TRIM(TO_JSON_STRING(t), '{}'), ',"')) kv,   
  UNNEST([STRUCT(SPLIT(kv, '":') AS x)])
  )
GROUP BY key

The above code saves me from specifying all column names or looping over them. But this throws the error 'Array index 1 is out of bounds (overflow)'. Without referencing x[1] (which contains the value for each row-column), the code works fine. I tried adding 'where x[1] is not null', etc., but everytime the code throws the above error.

Will appreciate any help. Can elaborate more if needed.

Test_Analytics · Accepted Answer · 2024-03-17 12:05:30Z

0

Using x[safe_offset(1)] worked!

answered Mar 17, 2024 at 12:05

Test_Analytics

275 bronze badges

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

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

BigQuery: Array index 1 is out of bounds (overflow) [Finding distinct counts for ALL columns without specifying column names)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related