Unable to format trino sql query in expected format

Question

I Have a query-

WITH dataset(ns, tid, nid, type) AS ( values ('PQR',  'ITKT20254',  'A','X'),
                                        ('PQR', 'ITKT20223',    'A','X'), 
                                        ('PQR', 'ABCD23456',    'B','X'), 
                                         ('PQR', 'ABCD54321',   'B','X'), 
                                        ('PQR', 'ITKT21111',    'A','X'),
                                        ('PQR', 'ITKT20000',    'A','Y')
                                         )
 select ns,nid, 
    cast(row(tradelist , res1) as row(tid array(varchar),res varchar)) as finalMap 
from
  (select ns,nid,tradelist,res1 
   from (
      select ns,nid,
          array_agg(cast(tid  as varchar)) as tradelist,
          'not include' as  res1 from
          (select ns,nid,tid from  dataset where type='X' )
       
      group by ns, nid 
     union 
      select ns,nid, 
          array_agg(cast(tid  as varchar)) as tradelist,
          'include' as  res1  from
           (select ns,nid, tid from  dataset where type='Y' )
       
      group by ns, nid
    )
   )

Getting the result-

ns      nid      finalMap
PQR     A   {tid=[ITKT20254, ITKT20223, ITKT21111], res=not include}
PQR     A   {tid=[ITKT20000], res=include}
PQR     B   {tid=[ABCD23456, ABCD54321], res=not include}

Expected output-

ns      nid      finalMap
PQR     A   [{tid=[ITKT20254, ITKT20223, ITKT21111], res=not include},{tid=[ITKT20000], res=include}]
PQR     B   [{tid=[ABCD23456, ABCD54321], res=not include}]

I'm trying to modify the query to get above format. but getting errors in array_agg function

Answer 1

Not sure why you have not used my previous answer but for this one try:

select ns, nid,
  transform(array['include', 'not include'] -- "generated" values 
      ,t -> cast(row(tids, t) as row(tid array(varchar), res varchar))) as finalMap
from (select ns,
             nid,
             array_agg(tid) tids
      from dataset
      group by ns, nid);

Since you generate the include/not include pair you can just use transform to generate corresponding rows

Output:

ns	nid	finalMap
PQR	A	[{tid=[ITKT20254, ITKT20223, ITKT21111], res=include}, {tid=[ITKT20254, ITKT20223, ITKT21111], res=not include}]
PQR	B	[{tid=[ABCD23456, ABCD54321], res=include}, {tid=[ABCD23456, ABCD54321], res=not include}]

UPD

Just add grouping and array_agg:

 select ns,nid,
    array_agg(cast(row(tradelist , res1) as row(tid array(varchar),res varchar))) as finalMap
from
  (select ns,nid,tradelist,res1
   from (
      select ns,nid,
          array_agg(cast(tid  as varchar)) as tradelist,
          'not include' as  res1 from
          (select ns,nid,tid from  dataset where type='X' )

      group by ns, nid
     union
      select ns,nid,
          array_agg(cast(tid  as varchar)) as tradelist,
          'include' as  res1  from
           (select ns,nid, tid from  dataset where type='Y' )

      group by ns, nid
    )
   )
group by ns, nid

Output:

ns	nid	finalMap
PQR	A	[{tid=[ITKT20254, ITKT20223, ITKT21111], res=not include}, {tid=[ITKT20000], res=include}]
PQR	B	[{tid=[ABCD23456, ABCD54321], res=not include}]