I Have a query-

WITH dataset(ns, tid, nid) AS ( values ('PQR',  'ITKT20254',  'A'),
('PQR', 'ITKT20223',    'A'),
('PQR', 'ABCD23456',    'B'),
('PQR', 'ABCD54321',   'B'),
('PQR', 'ITKT21111',    'A') )
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 dataset group by ns, nid union select ns,nid, array_agg(cast(tid  as varchar)) as      tradelist,'include' as  res1 from dataset group by ns, nid
))

Getting the result-

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

Expected output-

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

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