query the records from each snapshot id in profile_snapshot_export_

[Pradeep-Jaiswal]

profile_snapshot_export maintains the snapshot ids for last 10 days of snapshot. is it possible to query a specific record say based on ECID and find all the respective other fields values in each snapshot id over period of time ?

[SameerDu]

I created  an external table in redshift and did something like the following query 

Try this 

SELECT
identityMap,
json_extract_path_text(json_serialize(identityMap)::text, 'ecid', '0', 'id') AS ecid_id1,
json_extract_path_text(json_serialize(identityMap)::text, 'ecid', '1', 'id') AS ecid_id2,
json_extract_path_text(json_serialize(identityMap)::text, 'ecid', '2', 'id') AS ecid_id3,
json_extract_path_text(json_serialize(identityMap)::text, 'ecid', '3', 'id') AS ecid_id4
FROM
schema_external.profile_store_123123123;

[Sukrity_Wadhwa]

Hi @Pradeep-Jaiswal,

Was the given solution 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!