Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

PARSE Data returned by API

(0) ShareShare
ReportReport
Posted on by 38

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

 

  • LeeHarris Profile Picture
    LeeHarris 1,026 on at
    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.

  • AInglis Profile Picture
    AInglis 38 on at
    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

  • LeeHarris Profile Picture
    LeeHarris 1,026 on at
    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"
    }
    }
    }
    }
    }
    }
  • AInglis Profile Picture
    AInglis 38 on at
    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

  • LeeHarris Profile Picture
    LeeHarris 1,026 on at
    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.

  • AInglis Profile Picture
    AInglis 38 on at
    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

  • v-bacao-msft Profile Picture
    v-bacao-msft on at
    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,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard

Featured topics