Salesforce Connector not parsing JSON for "deep" relationship queries

[dalym]

Designer version: 2019.2.5.62427

SalesforceInput_4.1.0

I have a relationship query that is not being parsed. I am getting the raw JSON back as the result. Has anyone run into this issue? Is there a workaround or a fix in the works to address this?

Query not being parsed:

SELECT Username,
FederationIdentifier,
Contact.FirstName,
Contact.LastName,
CompanyName,
Contact.Account.Name,
Title,
Email,
Phone,
LastLoginDate,
Profile.Name,
ContactID
FROM User
WHERE Profile.Name = 'System Administrator'
AND IsActive = TRUE

Query being parsed:

SELECT Username,
FederationIdentifier,
Contact.FirstName,
Contact.LastName,
CompanyName,
Title,
Email,
Phone,
LastLoginDate,
Profile.Name,
ContactID
FROM User
WHERE Profile.Name = 'System Administrator'
AND IsActive = TRUE

The only difference is the first query contains "Contact.Account.Name"...

[dalym]

Update:

So it looks like parsing will work for one layer but not more than one. I'm running into this same issue with another workflow I'm working on...

[geraldo · Accepted solution]

Hi,

When using the Custom Query option the result is a raw Json unlike Query Builder. Here's a workflow to generate the json extraction resultgaod for you Replace the Salesforce Input tool with yours with the query that is returning json. attached workflow

[dalym]

geraldo,

Thanks for this. I was able to get this to work with one side effect and a question. The side effect is that the Crosstab tool converts the "." in the field name to a "_". I think I can live with this.

Your comment below ("When using the Custom Query option the result is a raw Json unlike Query Builder.") is not accurate. I have other custom queries that are parsed correctly by the connector which leads to my question: since some queries are parsed correctly, will I have to implement this only when I see that the results are not parsed correctly? This seems problematic. As a result, I've opened a support call with Alteryx to address this within their connector...

I do have a current workaround for now and I thank you for that!

[geraldo]

Hi,

When you use "Contact.Account.Name", "Contact.FirstName" and "Contact.LastName" it's just like a traditional SQL join. In cases that do not "join" If you go through Custom Query without checking "Attempt to Parse JSON Response" option the result is a JSON if marking the result will be in columns. In the cases of "join" I only got results in JSON. The replace of "." for "_" was due in the construction of the final columns there was a problem truncating columns.

Thanks for sharing knowledge!

[]

[dalym]

As an FYI, I did have the "Attempt to Parse JSON Response" option checked. The feedback I got from Alteryx is the key word here is "attempt". They do not guarantee the result will be able to be parsed... so it looks like your solution is the solution and I will mark it as such.

Thanks again for your help!