Exporting Profile in Segments into CSV

[akwankl]

Hi there,

I came across this really useful query:

select * from profile_snapshot_export_XXXXX  where segmentmembership['ups']['195329af-1df-49f0-993a-4d3495132e6'].status IN ('realized', 'existing','exited')

from this community post: Post 

When I run the query, it satisfies my basic needs of knowing who is in the segment. Though I do have couple questions:

1. What is "UPS" as mentioned in the query?
2. Here is a sample response from running the query, I am not SQL expert, is it possible to:
   1.  Figure out what the fields are for the NULLs under the IdentityMap & SegmentMembership column, what would the query look like?
   2. Is it possible to only extract the UUID portion of the IdentityMap?
   3. 
3. How do I export this data as a CSV?

Thank you!

[_Manoj_Kumar_]

Hello @akwankl 

1. You can extract the ecid or any identities like this.

SELECT identityMap['ecid']['id']  from profile_snapshot_export_XXXXXX ;

2. To understand the field's structure, look at the json of the profile attributes that will help you write/understand a query. I generally look at the templates to know how they work. You can find some sample queries here: Dataset query examples | Adobe Journey Optimizer

3. Once you have another query ready then you can store the output of the query in a dataset and then export the dataset to any cloud storage. More information is available here: Export datasets to cloud storage locations | Adobe Journey Optimizer