expolded identityMap in one row instead of for each ids | Community
Skip to main content
Pradeep-Jaiswal
Pradeep-Jaiswal
Level 5
August 5, 2025
Solved

expolded identityMap in one row instead of for each ids

  • August 5, 2025
  • 2 replies
  • 2993 views

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

Best answer by 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

2 replies

itsMeTechy
itsMeTechyAccepted solution
Level 4
August 7, 2025

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
    Community Manager
    Sukrity_WadhwaCommunity Manager
    Community Manager
    August 26, 2025

    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!

    Sukrity Wadhwa
    Terms & ConditionsAccessibility statement

    Loading footer...