How do I split a row into multiple columns in Presto?

user_2633577 0
Viewed 1275

I have a table like below with columns A (string) and B ROW(int):

A   B
a   [1,2,3]
b   [0,0,5]
c   [3,1,4]

And I would like to split the second column (which contains a ROW type) into multiple columns this way:

A   B1 B2 B3
a   1  2  3
b   0  0  5
c   3  1  4

I believe this can be done with a SQL statement, but unnest will create new rows (which I don't want) and split_part does not work with the ROW type. How can I achieve this?

trino presto sql
2 Answers

Assuming column B is a ROW type, you can use the .* ROW operator in SELECT statements.

This syntax exists in Trino (formerly PrestoSQL) but not PrestoDB, so it depends on which version you're using for this to work. I'd recommend moving to Trino anyways if you haven't already.

Copy/Paste:

WITH t(a, b) AS (
  VALUES
    ('a', ROW(1,2,3)),
    ('b', ROW(0,0,5)),
    ('c', ROW(3,1,4))
  )
SELECT 
  a as A,
  t.b.* AS (B1, B2, B3)
FROM t;

Execution:

trino> WITH t(a, b) AS (
    ->   VALUES
    ->     ('a', ROW(1,2,3)),
    ->     ('b', ROW(0,0,5)),
    ->     ('c', ROW(3,1,4))
    ->   )
    -> SELECT
    ->   a as A,
    ->   t.b.* AS (B1, B2, B3)
    -> FROM t;
 A | B1 | B2 | B3
---+----+----+----
 a |  1 |  2 |  3
 b |  0 |  0 |  5
 c |  3 |  1 |  4
(3 rows)
user_20238100

This is an equivalent answer to the chosen solution, with a slightly different syntax:

SELECT
A,
B[1] AS B1,
B[2] AS B2,
B[3] AS B3
FROM table;
user_26335770
Related