Sometimes there is a need to check what query is generated by xConnect search and passed to SOLR.
This article describes how the query can be found and replayed.
First of all, debug mode needs to be enabled in SOLR admin panel. It can be done as shown on the picture below:
After this change, the executed queries will be written to the SOLR log.
Here is an example of such query:
2019-09-06 09:05:44.148 DEBUG (qtp1330278544-196) [ x:sc901_xdb] o.a.s.s.s.LocalStatsCache ## GET {q=(x_type_s:(ContactDataRecord)+AND+_query_:("\{\!type%3Djoin+from%3Dcontactid_s+to%3Did\}\(x_type_s\:\(InteractionDataRecord\)+AND+_query_\:\(\"\\\{\\\!type%3Dparent+which%3Dx_type_s\\\:\\\(InteractionDataRecord\\\)\\\}\\\(path_s\\\:\\\(Events\\\)+AND+\\\(\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.SpamComplaintEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.BounceEvent\\\)+OR+@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailEvent\\\)\\\)\\\)\\\)\\\)\\\)\\\)\\\)+AND+\\\(definitionid_s\\\:\\\(2a65acc5985140dd851b23f7a6c53092\\\)+AND+messageid_s\\\:\\\(c7b8358d3fea4138b2b15d457bf4d2b6\\\)\\\)\\\)\\\)\"\)\)"))&df=_text_&echoParams=explicit&fl=id&cursorMark=*&json={"query":"(x_type_s:(ContactDataRecord)+AND+_query_:(\"\\{\\!type%3Djoin+from%3Dcontactid_s+to%3Did\\}\\(x_type_s\\:\\(InteractionDataRecord\\)+AND+_query_\\:\\(\\\"\\\\\\{\\\\\\!type%3Dparent+which%3Dx_type_s\\\\\\:\\\\\\(InteractionDataRecord\\\\\\)\\\\\\}\\\\\\(path_s\\\\\\:\\\\\\(Events\\\\\\)+AND+\\\\\\(\\\\\\(@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent\\\\\\)+OR+\\\\\\(@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.SpamComplaintEvent\\\\\\)+OR+\\\\\\(@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent\\\\\\)+OR+\\\\\\(@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent\\\\\\)+OR+\\\\\\(@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent\\\\\\)+OR+\\\\\\(@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent\\\\\\)+OR+\\\\\\(@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.BounceEvent\\\\\\)+OR+@odata.type_s\\\\\\:\\\\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailEvent\\\\\\)\\\\\\)\\\\\\)\\\\\\)\\\\\\)\\\\\\)\\\\\\)\\\\\\)+AND+\\\\\\(definitionid_s\\\\\\:\\\\\\(2a65acc5985140dd851b23f7a6c53092\\\\\\)+AND+messageid_s\\\\\\:\\\\\\(c7b8358d3fea4138b2b15d457bf4d2b6\\\\\\)\\\\\\)\\\\\\)\\\\\\)\\\"\\)\\)\"))","sort":"id+asc"}&sort=id+asc&rows=0&wt=json} |
If you are lucky and the query is not very complicated, the “q” parameter can be just copied and replayed on SOLR manually. However, with the query above, SOLR won’t be able to parse the passed data.
I perform the following actions to make the query working and more readable:
- Retrieve only “q” parameter value (everything which goes from “q=” to “&”).
The result will be the following:
(x_type_s:(ContactDataRecord)+AND+_query_:("\{\!type%3Djoin+from%3Dcontactid_s+to%3Did\}\(x_type_s\:\(InteractionDataRecord\)+AND+_query_\:\(\"\\\{\\\!type%3Dparent+which%3Dx_type_s\\\:\\\(InteractionDataRecord\\\)\\\}\\\(path_s\\\:\\\(Events\\\)+AND+\\\(\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.SpamComplaintEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent\\\)+OR+\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.BounceEvent\\\)+OR+@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailEvent\\\)\\\)\\\)\\\)\\\)\\\)\\\)\\\)+AND+\\\(definitionid_s\\\:\\\(2a65acc5985140dd851b23f7a6c53092\\\)+AND+messageid_s\\\:\\\(c7b8358d3fea4138b2b15d457bf4d2b6\\\)\\\)\\\)\\\)\"\)\)")) |
Decode the URL using some online decoder (for example: https://meyerweb.com/eric/tools/dencoder/)
The result will be the following:
(x_type_s:(ContactDataRecord) AND _query_:("\{\!type=join from=contactid_s to=id\}\(x_type_s\:\(InteractionDataRecord\) AND _query_\:\(\"\\\{\\\!type=parent which=x_type_s\\\:\\\(InteractionDataRecord\\\)\\\}\\\(path_s\\\:\\\(Events\\\) AND \\\(\\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent\\\) OR \\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.SpamComplaintEvent\\\) OR \\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent\\\) OR \\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent\\\) OR \\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent\\\) OR \\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent\\\) OR \\\(@odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.BounceEvent\\\) OR @odata.type_s\\\:\\\(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailEvent\\\)\\\)\\\)\\\)\\\)\\\)\\\)\\\) AND \\\(definitionid_s\\\:\\\(2a65acc5985140dd851b23f7a6c53092\\\) AND messageid_s\\\:\\\(c7b8358d3fea4138b2b15d457bf4d2b6\\\)\\\)\\\)\\\)\"\)\)"))
Remove the backslashes:
(x_type_s:(ContactDataRecord) AND _query_:("{!type=join from=contactid_s to=id}(x_type_s:(InteractionDataRecord) AND _query_:("{!type=parent which=x_type_s:(InteractionDataRecord)}(path_s:(Events) AND ((@odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent) OR (@odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.SpamComplaintEvent) OR (@odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent) OR (@odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent) OR (@odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent) OR (@odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent) OR (@odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.BounceEvent) OR @odata.type_s:(#Sitecore.EmailCampaign.Model.XConnect.Events.EmailEvent)))))))) AND (definitionid_s:(2a65acc5985140dd851b23f7a6c53092) AND messageid_s:(c7b8358d3fea4138b2b15d457bf4d2b6))))"))"))
Now the query is readable and can be analyzed. If you want to replay it as well, extra modifications are required.
Hashes can’t be parsed, so you need to replace them with the corresponding symbol code: %23
(x_type_s:(ContactDataRecord) AND _query_:("{!type=join from=contactid_s to=id}(x_type_s:(InteractionDataRecord) AND _query_:("{!type=parent which=x_type_s:(InteractionDataRecord)}(path_s:(Events) AND ((@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.SpamComplaintEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.BounceEvent) OR @odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailEvent)))))))) AND (definitionid_s:(2a65acc5985140dd851b23f7a6c53092) AND messageid_s:(c7b8358d3fea4138b2b15d457bf4d2b6))))"))"))
The query which I used as example has _query_ element, which must have value in quotes. The nested query also has its own inner _query_ element, which must be in quotes as well. As a result, the inner quotes must be properly encoded, otherwise the query will be messed up. I used %5C%22 to replace the inner quotes, which corresponds to \“ symbols:
(x_type_s:(ContactDataRecord) AND _query_:("{!type=join from=contactid_s to=id}(x_type_s:(InteractionDataRecord) AND _query_:( %5C%22{!type=parent which=x_type_s:(InteractionDataRecord)}(path_s:(Events) AND ((@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.SpamComplaintEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent) OR (@odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.BounceEvent) OR @odata.type_s:(%23Sitecore.EmailCampaign.Model.XConnect.Events.EmailEvent)))))))) AND (definitionid_s:(2a65acc5985140dd851b23f7a6c53092) AND messageid_s:(c7b8358d3fea4138b2b15d457bf4d2b6)))) %5C%22))"))
The query above can be executed against SOLR. It can be done by requesting the following URL in browser:
https://localhost:8984/solr/sc901_xdb/select?indent=on&q=TheQueryGoesHere
Where sc901_xdb is the name of the core, and the found query is passed using q parameter.