expolded identityMap in one row instead of for each ids

[Pradeep-Jaiswal]

lets says idenitymap in profileSnapshot has multiple ids. example below
[uid -> "{(NULL,2910574950527,NULL)}",mid -> "{(NULL,43602939,NULL)}",gid -> "{(NULL,ef481128-3054-4846-b23c-8f7650ce025b,NULL)}",vid -> "{(NULL,cookie_id_ef481128-3054-4846-b23c-8f7650ce025b,NULL)}"]

1) now if we query the select * from profileSnapshot we get one each row per profile having its own complete indeityMap.
see the output in the attachment

2) when we use select explode(idenitymap), * from profileSnapshot it would create 4 rows , one for each identity inside the idenitymap
see the output in the attachment

3) how do i write a query where each seperate row for the same profile returned as single row instead of multiple row ?
see the expected output in the attachment

[itsMeTechy]

when you have multiple values for the same identity namespace this may not work. But you can pivot the namespaces (by hard coding them) and get the count of identities in each namespaces and also display the identityMap as one column to always so you can refer the identity values from that. for doing this you need to group by an attribute for which you can use identitMap itself or you can use uuid function and get some random id assigned to each profile and use this attribute for grouping. i did something similar to this to get the count of identities in each namespace at profile level, to do analysis on profile collapse scenarios.

below query gives you a head start to do more calculation

with profiless as
(select uuid() as idd ,identityMap as im from <profile snapshot table> limit 10)
--select * from profiless
,exp_idm as
(select idd , explode(im) ,im from profiless)
--select * from exp_idm
,exp_val as
(select idd ,key
,case when key = 'ecid' then 1 else 0 end as ecidd
,case when key = 'email' then 1 else 0 end as emailidd
,case when key = 'phone' then 1 else 0 end as phoneidd
--you can add more case condition based on namespaces you use
,explode(value) ,value,im from exp_idm)
--select * from exp_val
,identities as
(select idd ,key ,row_number() over (partition by idd order by key) as rnm
,row_number() over (partition by idd ,key order by idd) as rnm_key
,ecidd ,emailidd ,phoneidd
,sum(ecidd) over (partition by idd) as ecidd_cnt
,sum(emailidd) over (partition by idd) as emailidd_cnt
,sum(phoneidd) over (partition by idd) as phoneidd_cnt
,keyy ,col.id ,value ,im from exp_val)
select * from identities

[Sukrity_Wadhwa]

Hi @Pradeep-Jaiswal,

Was the given solution by @itsMeTechy helpful to resolve your query or do you still need more help here? Do let us know. In case the given solution was helpful, then kindly choose it as the 'Correct Reply'.

Thanks!