CODEWITHSUNDEEP
sqlpresto
addicted
addicted

Reputation: 3051

Loop and transpose presto array column

I am having this array of rows column in my PrestoDB which I want to query through Redash (Business Intelligence viz tool) and transpose it. Let me illustrate my point better below:

Original Data:

id | array_col
--------------
1  | [[A,1],[B,2]]
2  | [[A,1]]

Resulting Data:

id  |  array_col_lbl1  |  array_col_val1  |  array_col_lbl2  |  array_col_val2
------------------------------------------------------------------------------
1   |         A        |         1        |        B         |        2

What I have tried:

select
id
, array_col[0].lbl1 as array_col_lbl1
, array_col[0].val1 as array_col_val1
, array_col[1].lbl1 as array_col_lbl2
, array_col[1].val1 as array_col_val2
from mytable

Obviously that method of mine does not work for array that contains only single element. It will throw Error running query: Array subscript out of bounds error.

Can anybody help?

Upvotes: 1

Views: 5495

Answers (1)

Ryan Bedard
Ryan Bedard

Reputation: 431

If the size of array_col can only be a fixed set of lengths then this solution should work for you. All you need to know is Presto will always try to throw errors instead of return nulls to you. One way to safely handle this is by checking the size (cardinality) of the array before accessing the index.

SELECT 
  id
  , IF(CARDINALITY(array_column)>0, array_column[1][1], NULL) AS array_col_lbl1
  , IF(CARDINALITY(array_column)>0, array_column[1][2], NULL) AS array_col_val1
  , IF(CARDINALITY(array_column)>1, array_column[2][1], NULL) AS array_col_lbl2
  , IF(CARDINALITY(array_column)>1, array_column[2][2], NULL) AS array_col_val2
FROM mytable

If you really want to be dodgy about you code Presto also supports the "TRY" UDF which will return NULL instead of throwing errors. This would work as well:

SELECT 
  id
  , TRY(array_column[1][1]) AS array_col_lbl1
  , TRY(array_column[1][2]) AS array_col_val1
  , TRY(array_column[2][1]) AS array_col_lbl2
  , TRY(array_column[2][2]) AS array_col_val2
FROM mytable

Although I do not recommend that solution as it can cover up other errors that you were not expecting to happen, which will make future debugging a nightmare.

If all of the arrays in your table can have variable lengths that are arbitrary, I would go a different route of trying to use a CROSS JOIN UNNEST https://prestodb.io/docs/current/sql/select.html

Upvotes: 1

Related Questions