query the records from each snapshot id in profile_snapshot_export_

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;