cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

PARSE Data returned by API

Hi

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

 

7 REPLIES 7
Highlighted
Community Support
Community Support

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:

333.gif

Run history:

28.PNG

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

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

Highlighted

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted

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.

 

Flow.jpg

 

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

Highlighted

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"
}
}
}
}
}
}

Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted

 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

Highlighted

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Users online (9,016)