Resultset rows into array Json [PrestoDB]

user_2939569 0
Viewed 282

I have a table where I have multiple rows against id. I want to convert each row as an entry to an array of array containing key-value pair in prestoDB using sql

id col1 col2 col3
1 2ad ff. sdfs
1 asf. erew dsds
1 vfdv dfds sdf

and I want the output to be something like this

id value
1 {{'col1':'2ad','col2':'ff','col3':'sdfs'},{'col1':'asf','col2':'erew','col3':'dsds'},{'col1':'vfdv','col2':'dfds','col3':'sdf'}}
... ....

with the below query I am able to achieve almost:

select id,  CAST( MAP(Array['col1','col2','col3'],Array [k."col1", 
k."col2", k."col3"]) As  Json)  as tt
from table k  order by 1;


|id| value|
|--|---- |
| 1| {'col1':'2ad','col2':'ff','col3':'sdfs'}|
|1|{'col1':'asf','col2':'erew','col3':'dsds'}|
|1|{'col1':'vfdv','col2':'dfds','col3':'sdf'}|
|...|....|

but I am still not able to concatenate based on ID as array_agg only works on a string and I don't know how to proceed

presto sql
1 Answers

with below query i was able to achieve it

 select mm.id ,CAST(array_agg(mm.tt) as Json) from (select nn."id" as id , nn.tt
 from(
select k."id",  CAST( MAP(Array['col1','col2','col3'],Array [k."col1", 
k."col2", k."col3"]) As  Json)  as tt
from table k  order by 1
)nn group by 1,2
)mm group by 1;
user_29395690
Related