AWS Kinesis Analytics SQL to transform rows to a list

user_4996042 0
Viewed 89

I have a Destination Stream that looks as follows for example :

Company_ID      Product      User_Tag    Count
   123            P1             T1        15
   123            P1             T2        10
   123            P1             T3        10

I want the Final Output as follows :

Company_ID      Product       User_Tag    Count
   123            P1         [T1,T2,T3]    35

I tried using listagg on User_Tag but I get an error saying no function signature found . Is this even possible on AWS Kinesis SQL on streaming data ?

postgresql-9.1 amazon-kinesis-analytics cloud amazon-web-services sql
1 Answers

SQL - Array Aggregate

SELECT company_id,
       product,
       Array_agg(user_tag),
       Sum(count)
FROM   stream
GROUP  BY company_id,
          product; 

 company_id | product | array_agg  | sum
------------+---------+------------+-----
        123 | P1      | {T1,T2,T3} |  35

SQL - String aggregate:

SELECT company_id,
       product,
       String_agg(user_tag, ','),
       Sum(count)
FROM   stream
GROUP  BY company_id,
          product; 
    

   company_id | product | string_agg | sum
------------+---------+------------+-----
        123 | P1      | T1,T2,T3   |  35

Edit Link to demo.

user_75256750
Related