aggregate column of type row

Question

I want to filter a column of rowtype and aggregate rowtypes when they have complement information. So my data looks like that :

|col1|rowcol                          |
|----|--------------------------------|
|1   |{col1=2, col2=null, col3=4}     |
|1   |{col1=null, col2=3, col3=null}  |
|2   |{col1=7, col2=8, col3=null}     |
|2   |{col1=null, col2=null, col3=56} |
|3   |{col1=1, col2=3, col3=7}        |

Here is some code you can use to have an working example:

    select col1, cast(rowcol as row(col1 integer, col2 integer, col3 integer))
    from (
    values 
        (1, row(2,null,4)),
        (1, row(null,3,null)),
        (2, row(7,8,null)),
        (2, row(null,null,56)),
        (3, row(1,3,7)) 
        ) 
    AS x (col1, rowcol)

I am expecting the result as following:

|col1|rowcol                         |
|----|-------------------------------|
|1   |{col1=2, col2=3, col3=4}       |
|2   |{col1=7, col2=8, col3=56}      |
|3   |{col1=1, col2=3, col3=7}       |

Maybe someone can help me...

Thanks in advance

Answer 1

You need to group them by col1 and process to merge not nulls, for example using max:

-- sample data
WITH dataset (col1, rowcol) AS (
    VALUES  
        (1, row(2,null,4)),
        (1, row(null,3,null)),
        (2, row(7,8,null)),
        (2, row(null,null,56)),
        (3, row(1,3,7)) 
) 

--query
select col1,
    cast(row(max(r.col1), max(r.col2), max(r.col3)) as row(col1 integer, col2 integer, col3 integer)) rowcol
from (
        select col1,
            cast(rowcol as row(col1 integer, col2 integer, col3 integer)) r
        from dataset
    )
group by col1
order by col1 -- for ordered output

Output:

col1	rowcol
1	{col1=2, col2=3, col3=4}
2	{col1=7, col2=8, col3=56}
3	{col1=1, col2=3, col3=7}