Aggregate values from different columns into an array

1

Hello!

I have the following need: given 3 float columns, create a column with an array containing these 3 floats.

So I made the following attempts (creating a minimal example here):

-- Create a table with H, S, and V columns
CREATE TABLE test_hsv_values AS
SELECT 
    CAST(RANDOM() AS FLOAT) AS h, -- Random float between 0 and 1 for hue
    CAST(RANDOM() AS FLOAT) AS s, -- Random float between 0 and 1 for saturation
    CAST(RANDOM() AS FLOAT) AS v  -- Random float between 0 and 1 for value
FROM (SELECT * FROM (VALUES(1),(2),(3),(4),(5))) AS t(dummy_column);

-- this does not work -- is there a better way?
select array[h, s, v] from test_hsv_values;

-- preparation step for the next approach
create table test_hsv_strings as (
select cast(concat('[', h, ',', s, ',', v, ']') as varchar) as color_hsv_string from "dremio-poc-jean"."dev-anna".test_hsv_values
);

-- this does not work with an error: AssertionError: Cannot add expression of different type to set
create table test_hsv as (
    select cast(convert_from(color_hsv_string, 'json') as list<float>) as color_hsv from "dremio-poc-jean"."dev-anna".test_hsv_strings
)

So I have a couple questions with this regard:

  1. Is there a more optimal way to make an array with values of several columns than through a to-and-back string convertation? Does the type of values matter – floats, integers, etc.?
  2. If not, and the only possible way is through this convert_to approach – how can I bypass the fact that even explicit type convertation is not enough to create a table? Pre-creation of a table is not an option, since in fact I’m using dbt, and this is just a minimal example of what is happening

In general, I would be glad to get any advice on how to achieve what I initially wanted – create an array with values of several columns (either all floats, or all integers).

Thank you!

2

Hi, the second SQL should have worked. I’ve asked the query planning team to take a look. I would try to avoid using CONVERT_FROM for this use case of constructing an array from a bunch of columns.

3

thank you for your response! what approach would you advise to use in this particular case when I want to construct an array from a fixed small number of columns?