PARSE Data returned by API

(0) Share

Report

Posted on by AInglis

I have an API call which returns data in the below form.

{

"Code1": "EmployerName1",

"": "",

"Code2": "EmployerName2",

"Code3": "EmployerName3"

}

From this response I want to be able to extract the "EmployerName" 's to compare with an SQL Table and inser new rows if neccesary.

Inserting the above as a sample to generate a schema does not result in the Employer Name being picked up as a variable I can use.

Any help with generating the correct schema would be appreciated and/or using a differernt flow action to achieve this.

Thanks

Categories:

Using connectors

I have the same question (0)

All responses (7)

Answers (0)

Sort by

v-bacao-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @AInglis,

It seems that this data has no special structure, that is, the properties value of Code corresponds to EmployerName.

You could use Parse JSON directly to extract data.

Image reference:

Run history:

Please take a try.

Best Regards,

Was this reply helpful? Yes No
AInglis 38 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the reply, but i'm not sure how this helps me insert the full list, which could be hundreds of Employers into a SQL database for comparison.

From your example I believe i would need to have a SQL Create Row action for each Employer in the list, which would be fine if the list was static and small.

Any other ideas would be appreciated

Was this reply helpful? Yes No
LeeHarris 1,026 on at

Like (1)

Report
Copy link

Link copied!

Hi @AInglis

Try using the following sample to generate the schema

{
"Employers":[
{"Name":"Andy"},
{"Name":"Bob"},
{"Name":"Charles"},
{"Name":"David"},
{"Name":"Edward"},
{"Name":"Francis"}
]
}

The [ ] braces indicate an array of items. This array contains a list of objects in { } braces. Each object has one property, "Name". You will be able to select the Name property in an action after Parse JSON and it will generate a loop for you, allowing you to handle multiple items easily with a single set of actions.

Was this reply helpful? Yes No
AInglis 38 on at

Like (0)

Report
Copy link

Link copied!

Thanks @LeeHarris,

This has brought me closer, however upon testing as an insert to Excel I get the following error

ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON_Response')?['Employers']' is of type 'Null'. The result must be a valid array.

I believe this is as a result of one of the returned rows being "": "",.

The schema that your example produced is as follows.

{
"type": "object",
"properties": {
"Employers": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Name": {
"type": "string"
}
},
"required": [
"Name"
]
}
}
}
}

Could you advise what i would have to do to allow null results from the array and also to include the Code field as another output from the response.

Thanks

Andrew

Was this reply helpful? Yes No
LeeHarris 1,026 on at

Like (0)

Report
Copy link

Link copied!
Hi @AInglis

Try with this schema instead. I have added a new property for Code and removed the section that defined Name as being a required field. Based on the error you are getting though, its possible that the response from the API doesn't match the schema. Are you able to share this API response (with sample data if necessary)?

{ "type": "object", "properties": { "Employers": { "type": "array", "items": { "type": "object", "properties": { "Name": { "type": "string" }, "Code": { "type": "string" } } } } } }

Was this reply helpful? Yes No
AInglis 38 on at

Like (0)

Report
Copy link

Link copied!

Hi @LeeHarris

The flow still has the same error when tested.

ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON_Response')?['Employers']' is of type 'Null'. The result must be a valid array.

Below is the first part of the API Response as per postman for your reference.

{
"421": "421 CONSULTING PTY LTD",
"": "",
"ADESANT": "ADESANT LEGAL HOLDINGS (AUSTRALIA) PTY LTD",
"ADWITECH": "ADWITECH PTY LIMITED",
"ALCATEL": "ALCATEL-LAMBERT AUSTRALIA LIMITED",
"ALPHAEEST": "ALPHAEAST SERVICES PTY LTD",
"ASTEK": "ASTEK CORPORATION",
"ANSB": "ANS Breast Cancer Trials Group",
"ANS": "ANS HEALTH",
"ARTX": "AROTEX",
"ABB": "ABB CORPORATION",
"ACS": "Angard Company Superannuation",
"AeWS": "Angard eWRAP Super",
"APS": "Angard Personal Superannuation",
"AUTU": "AUTURE PTY LTD",
"BELX": "BELBIN LIMITED",
"BETH": "BETHQHAN MINISTRIES",
"BRS": "BIATA RESPITE SERVICES",
"BRENNAN": "BRENNAN PTY LTD",
"BRIDGE": "BRIDGE AUSTRALIA PTY LTD",
"BROADCASTING": "BROADCASTING STATION PTY LTD",
"BRGP": "Brocer Road General Practice",
"BYRO": "BYRON BAY UNIT",
"CAFT": "CAFT Australease Pty Ltd",
"CALV": "CALVARY CENTRAL HOSPITAL",
"CCLX": "CENTRAL CITY LOCAL HEALTH DISTRICT",
"CCCX": "CETTNOCK CITY COUNCIL",
"CLASSIC": "CLASSIC CERAMICS IMPORTERS PTY LTD",
"CNCX": "COALFIELDS NEIGHBOUR CARE",
"CBA": "COMMONWEALTH BANK OF AUSTRALIA",
"CHC1": "Community HealthCare Pty Limited",
"CONP": "CONTASS HOUSING",
"CAUS": "CONTRAPTION AUSTRALIA",
"CDAH": "Community Disability Alliance Incorporated",
"CONC": "CONNECTED COMMUNITIES PTY LTD",
"CAHS": "COOTAMBLE HEALTH SERVICE",
"DATA": "DATATRACT PTY LTD",
"ELIN": "ELTE LINKS"
}

Thanks again for looking at this.
Andrew

Was this reply helpful? Yes No
LeeHarris 1,026 on at

Like (0)

Report
Copy link

Link copied!

Hi @AInglis

This is a valid error message. Although we have adjusted the schema in Flow to allow better processing of the data the JSON body being passed in is not in the same format and does not contain the Employer array.

Do you have any control over the API that you are calling and the response object it returns to you? If so, I would suggest making changes to the API so it returns data in the array format (that you have defined in Flow). In its current state, everytime you add a new Code/Employer key value pair to the API response you will need to change the flow to handle it as they are seperate properties. Changing to use the array of objects will make this much easier to iterate through and extend upon in future.

Was this reply helpful? Yes No