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

Question

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

Archaeopteryx · Answer

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

Archaeopteryx · Answer

Brandon

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

Thank you,

Chris

BrandonB · Answer

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