Parse JSON - no dynamic content from array

[Erron]

This one has me stumped. Im using a HTTP request to get JSON data which works find and then using 'Parse JSON' to get the data for an 'apply to each'.

When i test it the Parse JSON has a large amount of content in the output (all the data, no matter how i setup the schema). 

Problem is, no matter how i setup the schema i cannot see any options in the Dymanic Content option to use in the flow.

if i set the 'type' at the top of the schema to 'array', the flow runs fine but i have no dynamic content options in the flow. If i set the first 'type' to 'object' then i get the dynamic content options but then the Parse JSON doesnt work giving an error that it is expecting array but got object.

[LeeHarris]

Hi @Erron 

If you have a sample of the content that you are trying to parse, you might get more success by using the "use sample payload to generate schema" link in the Parse JSON action. Simply paste in your sample content and Flow will create the schema for you. I have found this to be pretty accurate in the past when it comes to generating a usable schema.

If that doesn't help, can you post your sample JSON content here and I'll be able to try it out in a test environment.

[Erron]

I did try the sample payload option but it makes it an object and then gives the error that its expect an array. same issue as posted above.

The JSON has a lot more entries than this one but this is the first 2 results. The only information im interested in getting out is the 'name' (which is a mobile number) and the department.name. 

[
  {
    "custom": null,
    "id": 2241709,
    "href": "https://api.samanage.com/other_assets/2241709-0400044852.json",
    "name": "040555555",
    "owner": {
      "group_id": 4778498,
      "is_user": true,
      "id": 4528769,
      "name": "Mark Hughes",
      "disabled": false,
      "title": "Service Technician",
      "email": "Mark.Hughes@dummydomain.com",
      "created_at": "2019-01-14T03:55:35.000Z",
      "updated_at": "2019-01-17T02:37:59.000Z",
      "last_login": null,
      "phone": "+61 8 1111111",
      "mobile_phone": "+61 1111111",
      "department": {
        "id": 136719,
        "name": "NAT Operations",
        "description": "",
        "default_assignee_id": null
      },
      "role": {
        "id": 454597,
        "name": "Requester",
        "description": "Requester role to view and submit service request.",
        "portal": true,
        "show_my_tasks": false
      },
      "salt": "9413e839d96d40eb16a5ffea019aa5064fb300e4",
      "group_ids": [
        4778498
      ],
      "custom_fields_values": [],
      "site": null,
      "avatar": {
        "type": "initials",
        "color": "#0089c9",
        "initials": "MH"
      },
      "mfa_enabled": false,
      "reports_to": {
        "group_id": 4708387,
        "is_user": true,
        "id": 4456475,
        "name": "Paul Welland",
        "email": "Paul.Welland@dummydomain.com",
        "avatar": {
          "type": "initials",
          "color": "#dfcd00",
          "initials": "PW"
        },
        "default_landing_page": "Last_Page_Viewed"
      },
      "default_landing_page": "Last_Page_Viewed"
    },
    "user": null,
    "description": "Business BYO Mobile Plan $59",
    "asset_id": "ID00628132241709",
    "site": null,
    "department": {
      "id": 162476,
      "name": "WA Operations",
      "description": "",
      "default_assignee_id": null
    },
    "asset_type": {
      "id": 1174939,
      "account_id": 62813,
      "parent_id": 0,
      "name": "Mobile Service"
    },
    "category": {
      "id": 1174939,
      "account_id": 62813,
      "parent_id": 0,
      "name": "Mobile Service"
    },
    "status": {
      "id": 1,
      "name": "Operational"
    },
    "ip_address": "",
    "manufacturer": "Telstra",
    "model": "SM-J530Y",
    "serial_number": "89610180002013438329",
    "created_at": "2019-07-22T06:38:50.000Z",
    "updated_at": "2019-07-24T02:35:20.000Z",
    "address": null,
    "tasks": [],
    "maintenance_contract": null,
    "lease_contract": null,
    "incidents": [],
    "purchase": null,
    "purchase_orders": [],
    "tags": [],
    "hardware_dates": [
      {
        "id": 54571378,
        "date": "2019-07-22",
        "date_type": "Assigned",
        "username": "Mark Hughes",
        "notes": "[Automatic] Technical contact changed to Mark Hughes",
        "user_type": null
      },
      {
        "id": 54680810,
        "date": "2019-07-24",
        "date_type": "Assigned",
        "username": "-1",
        "notes": "[Automatic] Assigned to NAT Operations department",
        "user_type": null
      },
      {
        "id": 54681521,
        "date": "2019-07-24",
        "date_type": "Assigned",
        "username": null,
        "notes": "[Automatic] Assigned to WA Operations department",
        "user_type": null
      }
    ],
    "latitude": null,
    "longitude": null,
    "custom_fields_values": [
      {
        "id": 93958397,
        "custom_field_id": 106086,
        "name": "Tag Printed",
        "value": "0",
        "options": "",
        "type": 3,
        "type_name": "Checkbox",
        "show_on_portal": false,
        "mandatory": false,
        "user": null
      }
    ]
  },
  {
    "custom": null,
    "id": 2241727,
    "href": "https://api.samanage.com/other_assets/2241727-0400341597.json",
    "name": "0400555555",
    "owner": {
      "group_id": 4556896,
      "is_user": true,
      "id": 4305128,
      "name": "Prabha Srinivasan",
      "disabled": false,
      "title": "Senior Application Engineer",
      "email": "Prabha.Srinivasan@dummydomain.com",
      "created_at": "2018-12-21T01:59:05.000Z",
      "updated_at": "2019-07-24T03:27:32.000Z",
      "last_login": "2019-02-14T06:49:40.000Z",
      "phone": "+61 8 81111111",
      "mobile_phone": "+61 1111111",
      "department": {
        "id": 162468,
        "name": "Proposals",
        "description": "",
        "default_assignee_id": null
      },
      "role": {
        "id": 454597,
        "name": "Requester",
        "description": "Requester role to view and submit service request.",
        "portal": true,
        "show_my_tasks": false
      },
      "salt": "9d04c42ffa99b5e11f5f5a515eb8572ed2015737",
      "group_ids": [
        4556896,
        4555151,
        4555156
      ],
      "custom_fields_values": [],
      "site": null,
      "avatar": {
        "type": "initials",
        "color": "#2eabcf",
        "initials": "PS"
      },
      "mfa_enabled": false,
      "reports_to": {
        "group_id": 4556836,
        "is_user": true,
        "id": 4305068,
        "name": "Brionne Gay",
        "email": "Brionne.Gay@dummydomain.com",
        "avatar": {
          "type": "initials",
          "color": "#fa7911",
          "initials": "BG"
        },
        "default_landing_page": "Last_Page_Viewed"
      },
      "default_landing_page": "Last_Page_Viewed"
    },
    "user": null,
    "description": "Business BYO Mobile Plan $59",
    "asset_id": "ID00628132241727",
    "site": null,
    "department": {
      "id": 162468,
      "name": "Proposals",
      "description": "",
      "default_assignee_id": null
    },
    "asset_type": {
      "id": 1174939,
      "account_id": 62813,
      "parent_id": 0,
      "name": "Mobile Service"
    },
    "category": {
      "id": 1174939,
      "account_id": 62813,
      "parent_id": 0,
      "name": "Mobile Service"
    },
    "status": {
      "id": 1,
      "name": "Operational"
    },
    "ip_address": "",
    "manufacturer": "Telstra",
    "model": "A1778",
    "serial_number": "89610180001753395352",
    "created_at": "2019-07-22T06:38:53.000Z",
    "updated_at": "2019-07-24T02:42:04.000Z",
    "address": null,
    "tasks": [],
    "maintenance_contract": null,
    "lease_contract": null,
    "incidents": [],
    "purchase": null,
    "purchase_orders": [],
    "tags": [],
    "hardware_dates": [
      {
        "id": 54571390,
        "date": "2019-07-22",
        "date_type": "Assigned",
        "username": "Prabha Srinivasan",
        "notes": "[Automatic] Technical contact changed to Prabha Srinivasan",
        "user_type": null
      },
      {
        "id": 54681529,
        "date": "2019-07-24",
        "date_type": "Assigned",
        "username": "-1",
        "notes": "[Automatic] Assigned to NAT Projects department",
        "user_type": null
      },
      {
        "id": 54681589,
        "date": "2019-07-24",
        "date_type": "Assigned",
        "username": null,
        "notes": "[Automatic] Assigned to Proposals department",
        "user_type": null
      }
    ],
    "latitude": null,
    "longitude": null,
    "custom_fields_values": [
      {
        "id": 93958798,
        "custom_field_id": 106086,
        "name": "Tag Printed",
        "value": "0",
        "options": "",
        "type": 3,
        "type_name": "Checkbox",
        "show_on_portal": false,
        "mandatory": false,
        "user": null
      }
    ]
  },
]

[v-yamao-msft]

Hi @Erron ,

Hope the following blog could be a reference for you:

http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow

I will do more research on this issue and back to you later.

Best regards,

Mabel

[LeeHarris]

Hi @Erron 

I simplified the schema and managed to get it so that the dynamic content is available. I think it was missing from your previous examples as your schema only included the object definitions of name and department and not the actual string values within them. The schema I used is below (there are some other properties still present which you may wish to remove).

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "custom": {},
            "id": {
                "type": "integer"
            },
            "href": {
                "type": "string"
            },
            "name": {
                "type": "string"
            },
            "department": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "integer"
                    },
                    "name": {
                        "type": "string"
                    },
                    "description": {
                        "type": "string"
                    },
                    "default_assignee_id": {}
                }
            }
        },
        "required": [
            "custom",
            "id",
            "href",
            "name",
            "department"
        ]
    }
}

Using this as dynamic content is tricky however as you have several properties with the same name, these being "id" and "name" at both the base level and within your department object. If possible, you may want to prefix your department level properties within the JSON that you receive e.g. department-name, department-id etc.

Hope that helps.

[Erron]

LeeHarris,

Almost there, it now adds department to a variable which is amazing, thank you.

Problem i have now is that the 2nd in the array has an empty 'department' and therefore gives this error:

[
  {
    "message": "Invalid type. Expected Object but got Null.",
    "lineNumber": 0,
    "linePosition": 0,
    "path": "[0].department",
    "schemaId": "#/items/properties/department",
    "errorType": "type",
    "childErrors": []
  }
]

I tried setting this to allow null but just got the same error

          "name": {
            "type": [
              "string",
              "null"
            ]

[LeeHarris]

Hi @Erron 

Try removing department from the required list of properties in the schema.

        "required": [
            "custom",
            "id",
            "href",
            "name",
            "department"
        ]

[Erron]

no luck unfortunately

[LeeHarris · Accepted solution]

Hi @Erron 

I have just tested this and it should work if you remove the "type" : "object" within the department of the schema.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "custom": {},
            "id": {
                "type": "integer"
            },
            "href": {
                "type": "string"
            },
            "name": {
                "type": "string"
            },
            "department": {
                "properties": {
                    "id": {
                        "type": "integer"
                    },
                    "name": {
                        "type": "string"
                    },
                    "description": {
                        "type": "string"
                    },
                    "default_assignee_id": {}
                }
            }
        },
        "required": [
            "custom",
            "id",
            "href",
            "name",
            "department"
        ]
    }
}

[Erron]

Perfect and all working, thank you so much for your help.

Just as a side not for anyone else, the nested item for another JSON entry i added still didnt have a dynamic content option so i created a variable and added the following to pick it up:

items('Apply_to_each_3')?['user']?['email']