How to encode an JSON array into the message body of an API call

[TeamData]

I am building a workflow that retrieves a list of IDs (strings) from a database and then queries those IDs via an API call.  That seems straight forward enough and I have confirmed that my API call via the Download tool is connecting and authenticating as desired.  The part I am stuck on is that the method for the API call is POST with the parameters in the message body which takes the form of arrays.

{
    "datasets": [ "public-global-fishing-events:latest", "public-global-encounter-events:latest" ],
    "startDate": "2017-01-01",
    "endDate": "2017-01-31",
    "vessels": [ "XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX" ]
}

 The first three items are static data that I would like to enter as constants (but don't know how to tell Alteryx to encode datasets as an array.

The fourth is a single value but still needs to be encoded as an array.

I have found other posts related to this that recommend using the JSON Create tool (which I downloaded and have available) but I don't know if it is actually pertinent to what I am trying to accomplish here.

Apologies for any vagueness, I am still quite new to the Alteryx community and tool.

[Felipe_Ribeir0]

Hi @TeamData 

To add arrays to the JSON, just add a .number after of the key, and the JSON build tool will do the rest of the job.

INPUT:

OUTPUT:

[TeamData]

Thanks for the response.  That's a great tip!  However, I am trying to figure out now how to make it so that only one vessel ID is encoded at a time instead of all of them.   The vessel ID is retrieved from a database table so it is a column of IDs.  

If I rename the column to 'Value' and append a new variable via Text Input as 'Name' -> 'vessel.1' then the JSON Build step puts all the vessel IDs into one large JSON string passed into the Download tool instead of one at a time.  

I'm sure this is just my inexperience with Alteryx.  Thank you for your suggestion!

[Felipe_Ribeir0]

Hi @TeamData 

If you mean that you need to make one JSON for each vessel, you can use the Group By option of the JSON Build tool. Check the attached workflow.

[TeamData]

Thanks for your followup and particularly for including the workflow attachments, very helpful.  I am almost there.  Now I am just having trouble getting the JSON correctly placed in the Download tool.  I tried using the JSON string as the body via the 'Take Query/String Body from field' option but that adds it to the body with a key of 'JSON' (e.g.  "JSON" : " value of encoded variables ") and what I want is just the value of the string used as the body.

I'm attaching my workbook if that is helpful. (removed my workbook because I left something in there...)

[Felipe_Ribeir0]

Hi @TeamData 

What system are you trying to connect with? Maybe taking a look at the official doc cold help. Anyway, give a try the attached workflow (there are 2 different tries inside of it).

[TeamData]

Thanks for your response.  I was busy with some tasks at work and couldn't respond to your suggests until today.  

Here is the connection I am trying to make: https://globalfishingwatch.org/our-apis/documentation#get-all-events-post-endpoint

I loaded your attached workflow.  The one path which uses the select tool to move the JSON into Payload, still has the problem that either the value is encoded as the 'key' or the 'value' in a JSON pair in the body, but what I need instead is the field's value just inserted into the body raw, not encoded like a JSON pair.  

e.g.   "JSON" : "{ "datasets" : "...." }"               doesn't work or

         "{"datasets" : "..." }" : {""}                         also doesn't work

The mc_download looks like it is a macro but I don't how to use it.  I looked in the Alteryx Community gallery but didn't see it.

[TeamData]

Hi, I managed to get a response from the server and wanted to post the update here.  One piece I was missing was including the

Content-Type: application/json  

 header.  I also am not currently using the JSON parsing tool but just using string manipulation in a field to construct the body.  

Now that I have confirmed I can get a response from the server I hope debugging will proceed more smoothly.  

I appreciate your help in working through this.

[Felipe_Ribeir0]

Hi @TeamData 

Good catch! You probably need to adjust just some more few things to make it work from here. Try to be sure that you are not missing anything that is mandatory in the official documentation and it shoud work. Good luck with that!