cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AInglis
Level: Powered On

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
Community Support Team
Community Support Team

Re: PARSE Data returned by API

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.
AInglis
Level: Powered On

Re: PARSE Data returned by API

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

Super User
Super User

Re: PARSE Data returned by API

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!


AInglis
Level: Powered On

Re: PARSE Data returned by API

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

Super User
Super User

Re: PARSE Data returned by API

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!


AInglis
Level: Powered On

Re: PARSE Data returned by API

 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

Super User
Super User

Re: PARSE Data returned by API

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!


Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 144 members 4,883 guests
Please welcome our newest community members: