How to use PARSE JSON with for each

[Bourmatnov]

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:

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

[Jcook]

Hello @Bourmatnov,

You don't need the Select action,

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

[Bourmatnov]

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.

[Jcook]

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:

2of3:

3of3:

[Jcook · Accepted solution]

@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.

[Bourmatnov]

@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!

[Jcook]

Hi @Bourmatnov 

No problem,

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