Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Zendesk query custom fields

I am using the Zendesk query component to copy data into the warehouse. What are the necessary changes to include the custom fields created in the "users" table in Zendesk as they don't show up in Data Selection within the component.

5 Community Answers

Matillion Agent  

Arawan Gajajiva —

Hello Arnob -

Can you please try the following:

  1. Project —> Manage API Profiles
    • Select “Zendesk” —> Click on Gear icon
    • Choose “users.rsd” under Files
    • Add the following as a new line after Line 37 (section that defines fields to read from Zendesk)
      • <attr name="custom_fields" xs:type="string" readonly="true" required="false" other:xPath="custom_fields" other:valueFormat="aggregate" />
  2. Click OK
  3. Go back into your Orchestration Job and re-create the Zendesk Query component (you can also right-click on the existing Zendesk Query component and select “Refresh Source Schema”)

At this point, you should see a custom_fields field now available in the component. When selected, this field should return the custom field data returned by Zendesk from Users.

Regards,
Arawan


Arnob Bordoloi —

This shows a new "custom_fields" column but does not transfer over any of the data from custom fields created in User table in Zendesk


Matillion Agent  

Arawan Gajajiva —

Hi Arnob -

Can you please try again with one slight change? The attribute name should be “user_fields”, not “custom_fields” as I had initially stated. So, to recap, can you please try as follows:

  1. Project —> Manage API Profiles
    • Select “Zendesk” —> Click on Gear icon
    • Choose “users.rsd” under Files
    • Add the following as a new line after Line 37 (section that defines fields to read from Zendesk)
      • <attr name="user_fields" xs:type="string" readonly="true" required="false" other:xPath="user_fields" other:valueFormat="aggregate" />
  2. Click OK
  3. Go back into your Orchestration Job and re-create the Zendesk Query component (you can also right-click on the existing Zendesk Query component and select “Refresh Source Schema”)

Please give this a try and let us know how it works out for you.

Best regards,
Arawan


Arnob Bordoloi —

Thank you Arawan!
This does give the custom field values in the following format:

"{
""consumer"": false,
""referred_others"": false,
""system::embeddable_last_seen"": null,
""user_role"": null
}"
under "user_fields" column
What is the best way to separate and pivot these values in the same table?


Matillion Agent  

Arawan Gajajiva —

Hi Arnob!

Glad that worked. As you can see, the data comes back as a JSON string. There are a few ways you could parse this data. One of the most common methods would be as follows:

  1. Store the “user_fields” data in a VARCHAR field in your target table. The data will be in the JSON format you have outlined.
  2. In a Transformation Job, you can send this data through a Calculator component. Within this component, you will find various native Redshift functions that you can apply to your data. More specifically, you will find a set of JSON Functions that you can use to parse and extract values from that JSON string.
  3. Once you have the fields parsed out, you could pivot the resulting data using the Transpose Rows component.

Hope this helps!

Arawan

Post Your Community Answer

To add an answer please login