cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bourmatnov
Frequent Visitor

How to use PARSE JSON with for each

I am having trouble parsing JSON with multiple output rows.  I use a "for each" and I can get see the individual JSON records come through while testing the flow but I don't seem to be able to reference the actual data from a compose in the "for each". Below is the JSON input I am trying to parse each employee row for. It's inside a simple initialize variable of type OBJECT. It must be an object because I am using this only to simulate a API sending JSON data which must be an object.  I could make the variable a array or string but that defeats the purpose of this example.

 

 

{
  "title": "aces_export_test",
  "fields": [
    {
      "id": "employeeNumber",
      "type": "employee_number",
      "name": "reference_number"
    },
    {
      "id": "workEmail",
      "type": "email",
      "name": "email"
    },
    {
      "id": "firstName",
      "type": "text",
      "name": "first_name"
    },
    {
      "id": "lastName",
      "type": "text",
      "name": "last_name"
    },
    {
      "id": "gender",
      "type": "gender",
      "name": "gender"
    },
    {
      "id": "supervisorId",
      "type": "text",
      "name": "approver"
    },
    {
      "id": "terminationDate",
      "type": "date",
      "name": "leaving_date"
    },
    {
      "id": "department",
      "type": "list",
      "name": "department"
    },
    {
      "id": "division",
      "type": "list",
      "name": "division"
    },
    {
      "id": "jobTitle",
      "type": "list",
      "name": "job_title"
    },
    {
      "id": "preferredName",
      "type": "text",
      "name": "preferred_name"
    }
  ],
  "employees": [
    {
      "id": "100",
      "employeeNumber": "1234",
      "workEmail": "tom@aol.com",
      "firstName": "David",
      "lastName": "Tom",
      "gender": "Male",
      "supervisorId": null,
      "terminationDate": "0000-00-00",
      "department": "MailOrder",
      "division": "Sales",
      "jobTitle": "Sr Sales Assoc",
      "preferredName": "Tommy"
    },
    {
      "id": "200",
      "employeeNumber": "1235",
      "workEmail": "ted@aol.com",
      "firstName": "Paul",
      "lastName": "Ted",
      "gender": "Male",
      "supervisorId": null,
      "terminationDate": "0000-00-00",
      "department": "MailOrder",
      "division": "Sales",
      "jobTitle": "Sr Sales Assoc",
      "preferredName": "Pablo"
    },
    {
      "id": "300",
      "employeeNumber": "1236",
      "workEmail": "bad@aol.com",
      "firstName": "Sally",
      "lastName": "Smith",
      "gender": "Female",
      "supervisorId": null,
      "terminationDate": "0000-00-00",
      "department": "MailOrder",
      "division": "Marketing",
      "jobTitle": "Principal",
      "preferredName": "Salamanca"
    }
  ]
}

 

 

 

Here is my flow I am trying to use to consume data:

Bourmatnov_0-1603130318449.png

The String above is one of many different attempts to extract the data that failed.  I was hoping that I could see the fields from output in the select flow but it won't show them to me.

 

Any help would be appreciated.  I would be happy to upload the flow itself it that is possible?

Thanks,

Paul

 

1 ACCEPTED SOLUTION

Accepted Solutions

@Bourmatnov 

 

First use this as your JSON schema in your Parse JSON action

{
    "type": "object",
    "properties": {
        "title": {
            "type": "string"
        },
        "fields": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "type": {
                        "type": "string"
                    },
                    "name": {
                        "type": "string"
                    }
                },
                "required": [
                    "id",
                    "type",
                    "name"
                ]
            }
        },
        "employees": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "employeeNumber": {
                        "type": "string"
                    },
                    "workEmail": {
                        "type": "string"
                    },
                    "firstName": {
                        "type": "string"
                    },
                    "lastName": {
                        "type": "string"
                    },
                    "gender": {
                        "type": "string"
                    },
                    "supervisorId": {},
                    "terminationDate": {
                        "type": "string"
                    },
                    "department": {
                        "type": "string"
                    },
                    "division": {
                        "type": "string"
                    },
                    "jobTitle": {
                        "type": "string"
                    },
                    "preferredName": {
                        "type": "string"
                    }
                },
                "required": [
                    "id",
                    "employeeNumber",
                    "workEmail",
                    "firstName",
                    "lastName",
                    "gender",
                    "supervisorId",
                    "terminationDate",
                    "department",
                    "division",
                    "jobTitle",
                    "preferredName"
                ]
            }
        }
    }
}

 

Next remove your Apply to each loop. and add a Compose action, now inside the Compose select dynamic content from the Parse JSON for an employee, for example firstName. An apply to each loop will then be created for you.

 


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

6 REPLIES 6
Jcook
MVP

Hello @Bourmatnov,

 

You don't need the Select action,

 

You can get the dynamic content just using a Compose action:

Jcook_0-1603136790899.png

 


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Hi @Jcook ,

 

That isn't working for me.  It looks like you are picking values form the PARSE JSON output one step back instead of the "current item" returned by the for each loop.   How do I reference the current items firstName for example? The one coming from the loop parsing the list?

 

It looks like it wont show me dynamic content for the item selected in the for loop.

If I try your suggestion it just tries to create a "for each 2" block again.

Hi @Bourmatnov 

 

I am picking items from the Parse JSON, but since the employees property is an array, the loop will loop through them

 

Since you're inside a loop everything will be current item, with different iterations of the loop:

1of3:

Jcook_0-1603142229002.png

 

2of3:

Jcook_1-1603142247684.png

 

3of3:

Jcook_2-1603142269174.png

 

 


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





@Bourmatnov 

 

First use this as your JSON schema in your Parse JSON action

{
    "type": "object",
    "properties": {
        "title": {
            "type": "string"
        },
        "fields": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "type": {
                        "type": "string"
                    },
                    "name": {
                        "type": "string"
                    }
                },
                "required": [
                    "id",
                    "type",
                    "name"
                ]
            }
        },
        "employees": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "employeeNumber": {
                        "type": "string"
                    },
                    "workEmail": {
                        "type": "string"
                    },
                    "firstName": {
                        "type": "string"
                    },
                    "lastName": {
                        "type": "string"
                    },
                    "gender": {
                        "type": "string"
                    },
                    "supervisorId": {},
                    "terminationDate": {
                        "type": "string"
                    },
                    "department": {
                        "type": "string"
                    },
                    "division": {
                        "type": "string"
                    },
                    "jobTitle": {
                        "type": "string"
                    },
                    "preferredName": {
                        "type": "string"
                    }
                },
                "required": [
                    "id",
                    "employeeNumber",
                    "workEmail",
                    "firstName",
                    "lastName",
                    "gender",
                    "supervisorId",
                    "terminationDate",
                    "department",
                    "division",
                    "jobTitle",
                    "preferredName"
                ]
            }
        }
    }
}

 

Next remove your Apply to each loop. and add a Compose action, now inside the Compose select dynamic content from the Parse JSON for an employee, for example firstName. An apply to each loop will then be created for you.

 


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





@Jcook,

 

Thanks for your help with this.  I finally got this working.  One addition to your schema, I needed to be able to handle null values in fields.  That required removing the string type by changing this:

                    "preferredName": {
                        "type": "string"
                    }

 

to this:

 

                    "preferredName": {}

 

(I tried adding [] around the type and added "null" as a second type but that did not work.)

 

I'm sure there is a way to specify a string and allow nulls but it escapes me. In any event, the above solution seemed to work....

 

Thanks for your help!

 

Hi @Bourmatnov 

 

No problem,

I have always removed the type, if a null value could come in. In my opinion its the easiest way.


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,228)