identityMap query from snapshot table

[Pradeep-Jaiswal]

profilesnapshot dataset has a field identityMap which has value in map structure.

This is one of the sample value of identityMap field from the snapshot.

[ecid -> "{(NULL,49790455655365226853605257450011089444,NULL)}",gid -> "{(NULL,3bcaebe5-6723-4822-abd4-2cfb22c4b908,NULL)}",vid -> "{(NULL,bacb8bd9d4aadef2d22482a829f1c149,NULL)}"] [ups -> "[795d22e5-ba6f-43e9-b8d8-e96f2be2e24c -> "(NULL,realized,2025-07-05 16:54:55.0)",73ffb7e5-7988-4108-9dd3-4403b2773915 -> "(NULL,realized,2025-07-05 16:54:55.0)"]"]

i want to query snapshot dataset for a specific ecid. i have tried different syntax (cast, unnest, json_extract etc) but none of them worked. this is one of the query i tried. whats the correct syntax to access map values in snapshot dataset ?

SELECT * FROM profile_snapshot_export_44b7518a_0a16_44bd_888c_891a9999ef0a where
JSON_EXTRACT(identityMap, '$.ecid[1]') = '49790455655365226853605257450011089444'

[FilipeFreitas · Accepted solution]

Hi Pradeep-Jaiswal,

to query a map field in the query service, you can use identitymap['ecid'][0].id. This is the easiest way in my oppinion. Note that it is extracting the array at the position 0 and the key (ecid) is case sensitive.

There is a second option, which is using explode: explode(identityMap.ecid.id). However, note that you can only have one explode per query.

Query examples:

--This will work:
SELECT
identitymap['ecid'][0].id.
, identitymap['yourCompanyIdentityKey'][0].id.
FROM profile_snapshot_export_xxxxx

--This will work and both columns will extract the ecid value:
SELECT 
identitymap['ecid'][0].id 
, explode(identityMap.ecid.id)
FROM profile_snapshot_export_xxxxx

--This will throw the UNSUPPORTED_GENERATOR.MULTI_GENERATOR error
SELECT
explode(identityMap.ecid.id)
, explode(identityMap.yourCompanyIdentityKey.id)
FROM profile_snapshot_export_xxxxx

Extra note: you can use Element_at(identityMap, 'ecid') to extract the entire ecid element (not only the id). Not what you want, but sharing anyway.

I hope this helps.

Best Regards,

Filipe C. Freitas

[Pradeep-Jaiswal]

This is great