Output value as an array of objects to an API

Hi all,

I’m currently creating an automation connected to an API. The output should be a datatable which would be used in a later stage. However, as I understand it, the datatable format is not readable for the API answer.

The way that I currently constructed automation in Studio is that I’ve put every value of the columns as an array of strings, see the following:

This gives the output to the API in the following format (in postman):

“OutputArguments”:
“{"Description":["DA","DB","DC","DD"],
"Unit":["UA","UB","UC","UD"],
"Available":["AA","AB","AC","AD"],
"AvailableOn":["yyyy-mm-dd","yyyy-mm-dd","yyyy-mm-dd","yyyy-mm-dd"],
"Product":["PA","PB","PC","PD"],
"Quantity":["Q","Q2","Q3","Q4"],
"ShipFrom":["SA","SB","SC","SD"]}”

However, the API developer that I am having discussions with wants to have the output in the format of array of objects, with each name of the variable included per row. For example:

[ { Description:“DA”, Unit:“UA”, Available: “AA”, AvailableOn:“yyyy-mm-dd”, Product:“PA”, Quantity: “QA”, ShipFrom: “SA” },
{ Description:“DA”, Unit:“UB”, Available: “AB”, AvailableOn:“yyyy-mm-dd”, Product:“PB”, Quantity: “QB”, ShipFrom: “SB” }
{ Description:“DA”, Unit:“UC”, Available: “AC”, AvailableOn:“yyyy-mm-dd”, Product:“PC”, Quantity: “QC”, ShipFrom: “SC” }
{ Description:“DA”, Unit:“UD”, Available: “AD”, AvailableOn:“yyyy-mm-dd”, Product:“PD”, Quantity: “QD”, ShipFrom: “SD” } ]

Does anyone have a good solution for this?

Thanks in advance!

@kkiw Basically in the output you are getting Json Object. You can use UiPath.Web.Activities to deserialize json and parse Json object to obtain datatable

I haven’t really worked much with json before. Could you please expand on what this means, and perhaps provide an example of how this is done?

@kkiw
Please check if the requirement was correctly understood:

Input:
grafik

expected output:

Hi Peter,

You are correct, that is the exact expected result that I am after. How did you reach this output?

Also, I am now being told that the input variable for the automation should also be json objects. So I suppose that some type of deserializing needs to be done as well?

@kkiw
used the JSON string, did some corrections (checking some double quotes to get a valid JSON) and deserialized it:

grafik

then within a nested loop the different properties where processed, accesing the value by a loop index and is constructing the row JObjects:

grafik

find starter help here:
ppr_JSONBox_kkiw.xaml (11.2 KB)

data_kkiw.json (298 Bytes)

1 Like

Wow you really put some time into this, thanks a lot!

I do have some other questions as well if you don’t mind.

The current workflow which you shared converts a json string into another. However, in my workflow, each variable is currently in the format of array of strings. How do I get to the same results starting from where I am? In other words: how do I go from this:

to the file named “data_kkiw.json”

Also, it seems like the input to the workflow should also be in the same format as that the result is. What should the variable type be?

Many thanks Peter!!

sure but lets try to scope to this initial topic, this will help researchers when looking for similar cases.

what is the real available input? From where are the arrays are populated?

The whole workflow is as follows:

  1. An api sends the input to the robot. The input is in the same format as the output of what you sent me (Jarray).
  2. The automation uses the input variables in a system in order to data scrape some information.
  3. This information is then used for a new output variable (which should be in the format you just sent me).

So my main question is how I get the data scraped information in the shape of a datatable to the output Jarray format.

I realise now that should have made a better title for this thread.

Thanks

have sent you a PM

@kkiw
Following up our discussions related to your requirements/scenario have a look here:

A sample datatable (we used special chars, quoted text …)
grafik

can be serialized to following result:
grafik

[{\"Column1\":\"123\",\"Column2\":\"ABÖÜ$\\#Ac\",\"Column3\":\"1\"},
{\"Column1\":\"456\",\"Column2\":\"Hello \\"Quote\\"!\",\"Column3\":\"OE\"}]

we do need the quotes for the column names (as these are strings) and do also need to escape some chars (e.g. the quotes from text with quote).

Give a try on:

  • Serialize the datatable with: JsonConvert.SerializeObject(dtData)
  • return the serialized string as out Argument of datatype: String
    ensure:
    grafik

let us know your feedback on the result.

This seems to have worked! You are a hero Peter, thank you very much for your effort that you put into this!

Actually… While in the workflow, this is the output that I receive:

“message”: “[{"Product":"2402427","Quantity":"3","Unit":"EA","Description":"Wheel Assy","Ship from":"EBREX/ÖREBRO","Available":"3","On":"05.11.2021"},{"Product":"2402427","Quantity":"2","Unit":"EA","Description":"Wheel Assy","Ship from":"KÖLN","Available":"2","On":"05.11.2021"}]”

However, the Output argument seems to add extra backslashes in the string. This is what the API receives:

“{"jsonOutput":"[{\"Product\":\"2402427\",\"Quantity\":\"3\",\"Unit\":\"EA\",\"Description\":\"Wheel Assy\",\"Ship from\":\"EBREX/ÖREBRO\",\"Available\":\"3\",\"On\":\"05.11.2021\"},{\"Product\":\"2402427\",\"Quantity\":\"2\",\"Unit\":\"EA\",\"Description\":\"Wheel Assy\",\"Ship from\":\"KÖLN\",\"Available\":\"2\",\"On\":\"05.11.2021\"}]"}”,

Note: I am not sure why, but when I post this, it removes extra backslash. For every backslash there is above, there should be three of them.

Is there any good way of converting the string variable to Json Array variable without those backslashes? That one seems to give back the correct lines to the API call.

Thanks in advance

are you looking for this?
grafik

If not, then please open a new topic on the new case. Thanks

No that’s not what I am looking for now. I will open a new topic!

For anyone interested, this is the new post:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.