Joining ticket_fields with tickets table via SQL

Hi Levon Galstyan and Greg Katechis, 

I would like to ask how you managed to connect and extract the data from Zendesk to Big query.

could you help me to pull the data from Zendesk to BiqQuery? 

Thank's in advance. 

The issue then isn't with the Zendesk APIs, the issue is with the ingestion phase of the raw data from our APIs. From what it sounds like, the schema is set for boolean instead of string on those fields, with nullable mode. Regardless of how it is actually set up, the only part that we can assist with is the API portion...if the API isn't returning the correct data, then we can definitely look into that. 

Thanks again Greg, but we checked and we saw several things :

- the columns in Zendesk are not null, but somehow get them as nulls in our warehouse

- most of them the values we get from this column are nulls, but when they are not null it's only TRUE or FALSE (which doesn't make sense for us as we need the exact value of the field and not a boolean)

- we just checked and that's the data we get in our raw tables from the Zendesk API before it gets to any transformation

We can not do that, because we basically get the data that zendesk provides us from the API. 

As you see on this screen (tickets table) the custom_fields_values are almost all nulls (when it's a boolean value it's not null though) - this WOULD eventually allow us to join the ticket_fields table and get the internal category for each event, but we don't know why Zendesk's API returns us null values for this, which means that the problem is certainly on your side.

Okay I see, thank you. So how could we do that? The schema is provided by Zendesk so for the moment I don't see any workarounds.

So, the issue here is still that you're putting the ticket fields into a separate table. There is no relational connection between the ticket fields and tickets outside of the ticket object itself, which means there won't be any way to join the two. Basically, the data returned from the ticket fields API has no concept of ticket data, it's just going to present the current state of the ticket fields and their associated metadata and option values, if any. This is why you'll need to create the schema in your tickets table to include the ticket fields that get passed in with your ticket data. 

Yassou Greg, 

Thanks for your message.

Here is what the ticket_fields table looks like so you understand and visualize the problem better.

Basically, the title column (the title of the custom ticket field we created) is an ARRAY where we have inside all kind of internal categories our agents work on to define the problem of the customer. 

The problem is that the id of these custom fields can't be joined to the ticket table

Let me know if it's clearer now, thanks!

Hi Levon! Rather than having two separate tables, I'd recommend that you import the ticket field data into your tickets table with a column for each ticket field. You can use the title of the ticket field to name the column and then it's just a query like: SELECT ticket_field_name FROM `db.dataset.table` WHERE ticket_id = 123

Apologies if I missed what you were asking and if I did, please let me know with some additional details about the use-case for putting ticket_fields in a separate table.