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

Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

Hi everyone,

sorry if this is not the appropriate place in the forum to post this question. I would like some help please if it is possible.

We have a PowerApps Application connected with CDS. In some CDS entities data are inserted through Microsoft Flows. For some  entities we have to use an API endpoint. We create the custom connector by uploading the JSON schema of the endpoint etc

The endpoint provides multiple actions. One of them is used in a Microsoft Flow to insert record in the cds entities.

In the first step we are calling the action "Get all vehicles with optional filters on name and ids" . In the next step we parse json. 

My first question is the following,because the json schema is a big one with many uncessary things, i am trying manually to shrink it for the fields records we need,but is updated from the api-docs from the enpoint. IS there any way to amend the json schema for a valid endpoint?

A sample record of the response is the following (is misformatted because the reall one has many more nested  objects)

  "vechicles": [
      {
        "id": "78acf69c-246e-431f-b802-66b777b2645b",
        "ids": {
          "Xuvi": "8816120",
          "alircspa": "MFTX2",
          "regNbr1": "C115298304"
        },
        "name": "TEST VEHICLE",
        "typeCode": "TU",
        "registrationCountry": "GBR",
         "constructionEvent": {
          "occurrence": "1989-01-01T00:00:00Z"
        },
        "vechicleEngines": [
          {
            "roleCode": "MAIN",
            "powerMeasurementUnit": "KWT",
            "powerMeasurement": "1470"
          },
          {
            "roleCode": "AUX",
            "powerMeasurementUnit": "KWT",
            "powerMeasurement": "400"
          }
        ]
        "namePLN": "Test Vehichle",
]
}

So actually the response is a json with the following  structure : record 1 with field 1 field 2 etc and inside it, it has record 1 types (multiple records), record 1 characteristics (multiple records etc)

When i try to apply to each so i can start inserting in the relevant entites, Microsoft Flow selects as a start in the loop the secondary data entities as record 1 type, but the actual need is to insert first in the primary entity,and then for each record in it to insert to the secondary entities.

Can you please indicate me what am i doing wrong?

Thanks in advance

Kind regards

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

Hi @IronFelix 

 

Firstly you need to loop on the vehicles array

Within loop expressions will be your friend

 

To get "id": "78acf69c-246e-431f-b802-66b777b2645b",

expression:

item()?['id']

 

 

To get 

  "ids": {
          "Xuvi""8816120",
          "alircspa""MFTX2",
          "regNbr1""C115298304"
        }

expression:

item()?['ids']?['Xuvi']

item()?['ids']?['regNbr1']
 
 
To get 
  "vechicleEngines": [
          {
            "roleCode""MAIN",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""1470"
          },
          {
            "roleCode""AUX",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""400"
          }
        ]
 
Since this is an array - you would have to loop again
for the loop use
item()?['vechicleEngines']
 
and within that loop to fetch data use
item()?['roleCode']
item()?['powerMeasurementUnit']
item()?['powerMeasurement']
 
 
And one more thing - you should be posting this in the Flow forum 🙂
 
--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

 

 

View solution in original post

6 REPLIES 6
Dual Super User
Dual Super User

Re: Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

Hi @IronFelix 

 

Which nodes do you want to read from this JSON

 "vechicles": [
      {
        "id""78acf69c-246e-431f-b802-66b777b2645b",
        "ids": {
          "Xuvi""8816120",
          "alircspa""MFTX2",
          "regNbr1""C115298304"
        },
        "name""TEST VEHICLE",
        "typeCode""TU",
        "registrationCountry""GBR",
         "constructionEvent": {
          "occurrence""1989-01-01T00:00:00Z"
        },
        "vechicleEngines": [
          {
            "roleCode""MAIN",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""1470"
          },
          {
            "roleCode""AUX",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""400"
          }
        ]
        "namePLN""Test Vehichle",
]
}
 
Highlight the data you need to fetch
IronFelix
Level: Powered On

Re: Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

Thanks a lot @RezaDorrani  for your time 

I have highlighted with red font the needed data. So actually i want to :

  1. Insert a new record cds entity Vehicle with id "id""78acf69c-246e-431f-b802-66b777b2645b",   "Xuv i": "8816120", "regNbr1": "C115298304"
  2. Go then to cds entity Vehicle Engines that has four custom fields, Vehicle (Lookup to the above entity) and pass the id and make insert of two new records  
  3. so we will have 2 records  in cds entity Vehicle Engines with the values Record 1 : Vehicle id, "roleCode""MAIN""powerMeasurementUnit""KWT" "powerMeasurement""1470"
    Record 2 : Vehicle id,    "roleCode"AUX", "powerMeasurementUnit""KWT", "powerMeasurement""400"
     
 "vechicles": [
      {
        "id""78acf69c-246e-431f-b802-66b777b2645b",
        "ids": {
          "Xuvi""8816120",
          "alircspa""MFTX2",
          "regNbr1""C115298304"
        },
        "name""TEST VEHICLE",
        "typeCode""TU",
        "registrationCountry""GBR",
         "constructionEvent": {
          "occurrence""1989-01-01T00:00:00Z"
        },
        "vechicleEngines": [
          {
            "roleCode""MAIN",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""1470"
          },
          {
            "roleCode""AUX",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""400"
          }
        ]
        "namePLN""Test Vehichle",
]
}
 
Dual Super User
Dual Super User

Re: Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

Hi @IronFelix 

 

Firstly you need to loop on the vehicles array

Within loop expressions will be your friend

 

To get "id": "78acf69c-246e-431f-b802-66b777b2645b",

expression:

item()?['id']

 

 

To get 

  "ids": {
          "Xuvi""8816120",
          "alircspa""MFTX2",
          "regNbr1""C115298304"
        }

expression:

item()?['ids']?['Xuvi']

item()?['ids']?['regNbr1']
 
 
To get 
  "vechicleEngines": [
          {
            "roleCode""MAIN",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""1470"
          },
          {
            "roleCode""AUX",
            "powerMeasurementUnit""KWT",
            "powerMeasurement""400"
          }
        ]
 
Since this is an array - you would have to loop again
for the loop use
item()?['vechicleEngines']
 
and within that loop to fetch data use
item()?['roleCode']
item()?['powerMeasurementUnit']
item()?['powerMeasurement']
 
 
And one more thing - you should be posting this in the Flow forum 🙂
 
--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

 

 

View solution in original post

IronFelix
Level: Powered On

Re: Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

Thanks a lot @RezaDorrani , this is a solution. If i am not abusing your time and effort, can you give me a last help with the definition language

The real body of the answer to the request is coming in the simplest format with this structure, so my expression should be

Item()?['embedded']?['vehicles']?['id'] ? 

Anyway thanks a lot for your help and much appreciated

{
"_embedded": {
    "vehicles": [
      {
        "id": "78acf69c-246e-431f-b802-66b777b2645b"
}
]
}
}
Dual Super User
Dual Super User

Re: Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

Vehicles is an array

So either Through it
Item()?['embedded']?['vehicles']

Or if u want array item at position 1
Item()?['embedded']?['vehicles']?[0]?['id']
IronFelix
Level: Powered On

Re: Microsoft Flow: Help to Parse Json with nested objects from custom connector through Import Open API File

I am doing something tottaly stupid, i am getting null values all time 😞 Capture.PNG

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 307 members 3,317 guests
Please welcome our newest community members: