Leveraging Customer Profile Array Data for export in Destination

[MichaelBailey]

Is there any way to export Customer Profile attributes contained within an Array?

Simply setting up a destination and selecting a field from the array doesn't make sense and even using index notation doesn't work (and wouldn't solve our use case anyway):

Is there some work around or process for accessing these values?

For example, we create a Segment for customers who have 'checking' accounts with a balance of less than 100.

This Segment includes 2 Customer Profiles that contain the following data:

{

    "id": "111",

       "account": [

       {

          "accountNumber": "AAA1",

          "accountBalance": 75,

          "accountType": "checking"

       },

       {

          "accountNumber": "AAA2",

          "accountBalance": 50,

          "accountType": "savings"

       },

       {

          "accountNumber": "AAA3",

          "accountBalance": 50,

          "accountType": "checking"

       }

    ]

},

{

    "id": "222",

    "account": [

       {

          "accountNumber": "BBB1",

          "accountBalance": 25,

          "accountType": "checking"

       },

       {

          "accountNumber": "BBB2",

          "accountBalance": 100,

          "accountType": "savings"

       }

    ]

}

If we want to create an extract that contains the customer id, accountNumber, and account Balance the data in the extract would look something like:

id | accountNumber | accountBalance

111 | AAA1 | 75

111 | AAA3 | 50

222 | BBB1 | 25

Is there any way to create an extract similar to this?

Thanks in advance for any help you can provide!

[adobechat]

HI @MichaelBailey Yes, you can achieve this using AEP Query Service !

Given your JSON data structure, you would use a SQL query with the CROSS JOIN UNNEST() function to flatten the nested array. Try something like this:-

SELECT 
    id,
    accounts.accountNumber,
    accounts.accountBalance
FROM
    your_table_name,
    CROSS JOIN UNNEST(account) AS accounts
WHERE 
    accounts.accountType = 'checking' AND 
    accounts.accountBalance < 100

Hope this solves your problem.

Thanks

Madhan 

[MichaelBailey]

Thanks for your response Madhan that is really good info.

This is useful for querying a dataset to create a dataset that contains this information, but doesn't solve the problem of accessing Customer Profile attributes or leveraging Segments.

For example, this will return all records in the dataset that meet the criteria (checking < 100) which could be inaccurate when compared to the data that exists in Customer Profiles.

[adobechat · Accepted solution]

Hi @MichaelBailey 

Yes, you are correct but the extraction of specific array data directly from profile attributes to a destination is not supported by Adobe. What I have suggested is a workaround using the Query Service. Unfortunately, this is the closest you can get as what you are after is not yet supported by Adobe Experience Platform as far as I know.

You could raise a support ticket with Adobe to confirm and let me know :).

Thanks

Madhan

[sharmilap]

Hi @MichaelBailey ,

Not sure if this still needs to be resolved for you. We have a similar use case and this functionality seems to work now. Please try the following for your source field and check:

_keybank.accounts[0].accountClosedDate

I selected these attributes using the upward arrow in the 'SOURCE FIELD' from Destination Mapping screen.

Thanks.