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

[Anonymous]

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

[RezaDorrani]

Hi @Anonymous 

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

[Anonymous]

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

]

}

[RezaDorrani · Accepted solution]

Hi @Anonymous 

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.

[Anonymous]

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"

}

]

}

}

[RezaDorrani]

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']

[Anonymous]

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

[mhammoud]

Can anybody represent calling the same scheme of JSON but using power apps. Basically I'm facing a problem with JSON response received from a flow and a need to render some objects from it inside a gallery. Here is a sample:

{

    "Recommendations": [

        {

            "category": "email",

            "confidence": 0.2552262545,

            "processes": [

                {

                    "type": "id",

                    "value": "5e6522e156d41b4b91cd94a0"

                }

            ],

            "response": "Please vist our documentation page at www.example.com"

        }]

}