APIs and JSON payload transferred as input to a table in a database

[Archaeopteryx]

Hi,

The scenario is I'm sending a Do Query API request to a table in a database via Alteryx. I send along a JSON Query string, I fill out the Headers tab, etc. 

I get the return JSON payload that I'm currently saving as a text.json file. 

The intension is to then use that JSON payload as input to another table using an Insert Records API. 

The field IDs in the payload will not/do not match the field IDs of where the data is going.

Can someone guide as to what is the appropriate way to resolve this, so that the data in the JSON upload is appropriately matched to their corresponding data fields in the destination table? 

Thank you for any guidance

Chris

[BrandonB]

Hi @Archaeopteryx 

This will depend on the API that you are working with and how the insert records call works. If it is expecting the data fields to match, I imagine that you could try one of two things.

1.) Use a find and replace tool to swap the column values in the JSON string with the new column values prior to sending it into the next Download tool

2.) Use a JSON Parse tool followed by logic to once again map the columns to the new column names and then use a JSON Build tool to recreate the JSON

In both scenarios it sounds like you need to standardize the column names to the names of the final destination. I am curious though, is there a reason that you are saving the JSON Payload to a json file? Can't you just use the JSON payload string in the same workflow for the next step of inserting the records?

Hopefully this helps!

[Archaeopteryx]

Hi Brandon,

Thanks for the reply.

Yes, I could use the payload in the same workflow for the next step - - - if I can overcome my initial problem. Then evolve the workflow to do just what you suggest. 

When I learn things that are new to me, I take bite size steps, examine results, make adjustments, test another bite size step, examine results, make adjustments. Doing new things in small increments galvanizes my learning of the new thing. The ultimate creation will have me avoiding doing any maintenance if that is possible. 

I have to be sure that the payload, where Field ID12 from the origin, will be matched to Field ID 22 in the destination. My venturing into this process raises the question of aligning/mapping data from Origin to Destination. 

[BrandonB]

Instead of outputting it to a JSON file maybe just output to a yxdb file as a temporary stepping point. Then I would start to test out the JSON Parse tool: https://help.alteryx.com/current/designer/json-parse-tool, then maybe a crosstab and a dynamic rename in order to standardize the fields, and then finally a JSON Build tool to rebuild the JSON: https://help.alteryx.com/current/designer/json-build-tool

[Archaeopteryx]

Brandon

I think I see where you're going with this. I'll try it out. 

Thank you,

Chris

[Archaeopteryx]

The JSON Build tool appears to be in beta and it does not give me the expected results. I'm assuming I attach the JSON Build tool to the output end of the JSON Parse tool? The configuration parameters of the JSON Build tool seem to require data points of the JSON Parse tool. 

Anyway, the ultimate goal is to API request for data, then, API request to send that data elsewhere. In the in between places, I need to modify that JSON to be acceptable input into a database table with fields aligned properly even when the field IDs of the input JSON do not match the field IDs of the destination table. 

As a backstory, which I probably should have provided, - - I have accomplished an API request for data from a Quick Base table using Alteryx and the Quick Base API. I do the JSON parsing, Cross-Tabbing, etc then finally direct the output to a flat file on OneDrive. 

My next evolutionary endeavor from that point is what I've already described previously. 

Thank you

Chris