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

Parse JSON - no dynamic content from array

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.parse_json_result.jpg

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.

 

invalid type.jpgparse_json_asobject.jpgparse_json.jpg

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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"
        ]
    }
}

 

 


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

Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

View solution in original post

9 REPLIES 9
LeeHarris
Solution Sage
Solution Sage

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.

 


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

Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Erron
Frequent Visitor

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
      }
    ]
  },
]

 

 

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

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

chrome_2019-07-25_09-51-34.png

 

Hope that helps.

 


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

Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Erron
Frequent Visitor

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

Hi @Erron 

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

 

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

 


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

Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Erron
Frequent Visitor

no luck unfortunately

 

gotnull.jpg

 

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"
        ]
    }
}

 

 


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

Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Erron
Frequent Visitor

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (2,491)