Fetching all rows that matches with batchID of _experience column from "ajo_message_feedback_event_dataset"

[ButhpurKiran]

Hi,

We have a huge set of data ingested into AEP for schema "ajo_message_feedback_event_dataset". And, our goal is to fetch the list of users who received emails and accessed the same. it's understood from Adobe documentation that information is stored at "ajo_message_feedback_event_dataset".

Whilst accessing the dataset through Querying with below SQL query, we can see the results with various columns

"SELECT *
FROM ajo_message_feedback_event_dataset;"

out of which, a specific column named "_experience" contains the 

batchInstanceID":"xxxxxxxxxxxxxxxxxxxxxxx", which helps us to build audience for a specific set of batchInstanceID.

Upon using the below query to filter the results that matches with batchInstaceID from _experience as below

SELECT *
FROM ajo_message_feedback_event_dataset
WHERE contains(_experience, "%batchInstanceID%");,

we see an error that describes on the field unavailable.

Error Message : 
ErrorCode: 08P01 queryId: c44e0d65-b42f-4f08-8de3-7f4962050c08 Unknown error encountered. Reason: [[UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `%batchInstanceID%` cannot be resolved. Did you mean one of the following? [`timestamp`, `_experience`, `eventType`, `_ACP_BATCHID`, `identityMap`].; line 4 pos 28; 'GlobalLimit 50000 +- 'LocalLimit 50000 +- 'Project [*] +- 'Filter 'contains(_experience#13531029, '%batchInstanceID%) +- SubqueryAlias ajo_message_feedback_event_dataset +- View (`ajo_message_feedback_event_dataset`, [timestamp#13531025,_ACP_BATCHID#13531026,_acp_system_metadata#13531027,_experience#13531029,identityMap#13531030,eventType#13531031,_id#13531032]) +- Project [timestamp#13531025, _ACP_BATCHID#13531026, _acp_system_metadata#13531027, _experience#13531029, identityMap#13531030, eventType#13531031, _id#13531032] +- Relation [timestamp#13531025,_ACP_BATCHID#13531026,_acp_system_metadata#13531027,_ACP_DATE#13531028,_experience#13531029,identityMap#13531030,eventType#13531031,_id#13531032] parquet ]

Has anyone experience the same and able to get the filtered results?

Thanks in advance!

Kind regards,

Kiran Buthpur

[DavidKangni]

@ButhpurKiran 

Try this

select * from ajo_message_feedback_event_dataset
where _ACP_BATCHID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX'

Thanks,

David

[ButhpurKiran · Accepted solution]

Thanks for the response David, the above query has given me error message as the _ACP_BATCHID is column not available. However, with further R&D, i am able to get the results using below query

SELECT count(*)
FROM ajo_message_feedback_event_dataset
WHERE _experience.customerJourneyManagement.messageExecution.batchInstanceID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx';

Below is explanation that may help the forum.

• _experience is a structured object, not a string—SQL LIKE or _ACP_BATCHID field mapping cannot be applied to it.
• You need to reference a specific field inside _experience, such as the batchInstanceID.

Context Detail

Use full XDM paths	Always reference specific nested fields, e.g. ...messageDeliveryfeedback.feedbackStatus, never _experience alone.
Match data types	batchInstanceID is a string (GUID), so use exact string comparison, not numeric or pattern matching.
Don’t use LIKE on objects	LIKE only works on text/string fields—not on nested objects or JSON.

Thanks 

Kind regards,

Kiran Buthpur

[DavidKangni]

@ButhpurKiran 

_ACP_BATCHID is available under ajo_message_feedback_event_dataset

You request was just not clear to me as I thought you were looking for batch ingested into ajo_message_feedback dataset and not for profiles who received a specific comms (messageExecution)

But the good news is you found a solution 🙂

Thanks,

David