cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seanmcn
Level: Powered On

JSON Parse Output: How to access properites from Child Object

Hi, 

I have source JSON with an array of "Entities", each Entity has several properties and one property is of type "Object" (see below). I am parsing the json below with the provided schema.  I can get any direct property of an Entity to render in the table, but what notation is required to get a property of an object, that is a property of an Entity? If I put status in as a column I end up with a chunk of JSON - since I'm parsing the object with the schema I'm thinking there must be a way to reference those properties so I can render them into a table.  Thanks in advance for any guidance! 

 

Flow: 

image.png

 

The desired output into table that would be something like this: 

Type

Status.LastExportDate

Status.LastExportStatus

Account

2018-12-01T11:51:02.6910357Z

Success

New_a

2018-06-13T20:51:33.1164211Z

Success

 

Actual Table using the above flow: 

Type

Status

Account

{"Type":"account","ProfileId":"898bbc15-5a77-4a8f-821a-9949fe3f226d","TotalNotifications":1647531,"SuccessNotifications":1647531,"FailureNotifications":0,"LastExportDate":"2018-12-01T11:51:02.6910357Z","LastExportStatus":"Success","EntityMetadataState":"Created","ExpectedInitialSyncNotifications":0,"InitialSyncState":"Completed","VersionNumber":28326699,"ChangeTime":"2018-12-01T11:50:56","MetadataVersionNumber":28283951,"MetadataChangeTime":"2018-11-30T14:17:03"}

New_a

{"Type":"new_a","ProfileId":"898bbc15-5a77-4a8f-821a-9949fe3f226d","TotalNotifications":33,"SuccessNotifications":29,"FailureNotifications":4,"LastExportDate":"2018-06-13T20:51:33.1164211Z","LastExportStatus":"Success","EntityMetadataState":"Created","ExpectedInitialSyncNotifications":0,"InitialSyncState":"CompletedWithFailures","VersionNumber":0,"ChangeTime":"0001-01-01T00:00:00","MetadataVersionNumber":22345017,"MetadataChangeTime":"2018-06-13T20:51:27"}

 

JSON: 

{
  "Type": "account",
  "ProfileId": "898bbd15-5a77-4a8f-821a-9949fe6f226f",
  "TotalNotifications": 1647531,
  "SuccessNotifications": 1647531,
  "FailureNotifications": 0,
  "LastExportDate": "2018-12-01T11:51:02.6910357Z",
  "LastExportStatus": "Success",
  "EntityMetadataState": "Created",
  "ExpectedInitialSyncNotifications": 0,
  "InitialSyncState": "Completed",
  "VersionNumber": 28326699,
  "ChangeTime": "2018-12-01T11:50:56",
  "MetadataVersionNumber": 28283951,
  "MetadataChangeTime": "2018-11-30T14:17:03"
}
{
  "Id": "898bbd15-5a77-4a8f-821a-9949fe6f226f",
  "Version": "2.0",
  "State": 1,
  "LastModified": "2017-03-30T21:26:36.143",
  "Name": "naosrtw_test",
  "OrganizationId": "e6f62f74-0000-390b-917b-ad40847cd01d",
  "Entities": [
    {
      "Type": "account",
      "Settings": {},
      "Status": {
        "Type": "account",
        "ProfileId": "898bbc15-5a77-4a8f-821a-9949fe3f226d",
        "TotalNotifications": 1647531,
        "SuccessNotifications": 1647531,
        "FailureNotifications": 0,
        "LastExportDate": "2018-12-01T11:51:02.6910357Z",
        "LastExportStatus": "Success",
        "EntityMetadataState": "Created",
        "ExpectedInitialSyncNotifications": 0,
        "InitialSyncState": "Completed",
        "VersionNumber": 28326699,
        "ChangeTime": "2018-12-01T11:50:56",
        "MetadataVersionNumber": 28283951,
        "MetadataChangeTime": "2018-11-30T14:17:03"
      },
      "InitialSyncMetadataCreatedTime": "2017-03-30T04:10:13.913",
      "AdditionTime": null,
      "InitialSyncDataCompletedTime": "2017-03-30T05:00:50.763",
      "LastDeltaSyncMetadataTime": "2018-11-30T14:17:05.86",
      "LastDeltaSyncDataTime": "2018-12-01T11:51:02.69",
      "InitialSyncProcessCompleteTime": "2017-03-30T04:17:12.327"
    },
    {
      "Type": "new_a",
      "Settings": {},
      "Status": {
        "Type": "new_a",
        "ProfileId": "898bbc15-5a77-4a8f-821a-9949fe3f226d",
        "TotalNotifications": 33,
        "SuccessNotifications": 29,
        "FailureNotifications": 4,
        "LastExportDate": "2018-06-13T20:51:33.1164211Z",
        "LastExportStatus": "Success",
        "EntityMetadataState": "Created",
        "ExpectedInitialSyncNotifications": 0,
        "InitialSyncState": "CompletedWithFailures",
        "VersionNumber": 0,
        "ChangeTime": "0001-01-01T00:00:00",
        "MetadataVersionNumber": 22345017,
        "MetadataChangeTime": "2018-06-13T20:51:27"
      },
      "InitialSyncMetadataCreatedTime": "2017-03-30T21:26:55.673",
      "AdditionTime": null,
      "InitialSyncDataCompletedTime": null,
      "LastDeltaSyncMetadataTime": "2018-06-13T20:51:33.1",
      "LastDeltaSyncDataTime": null,
      "InitialSyncProcessCompleteTime": "2017-03-30T21:27:00.157"
    }
  ],
  "DestinationType": 1,
  "DestinationKeyVaultUri": "https://hidden.vault.azure.net/secrets/org/secretguid",
  "DestinationPrefix": "something",
  "RetryPolicy": {
    "MaxRetryCount": 50,
    "IntervalInSeconds": 60,
    "Backoff": 0
  },
  "Status": {
    "ExportStatus": "Partial Failure",
    "InitialSyncState": "CompletedWithFailures",
    "TotalNotifications": 1647564,
    "SuccessNotifications": 1647560,
    "FailureNotifications": 4,
    "LastExportDate": "2018-12-01T11:51:02.6910357Z",
    "LastModifiedOn": "2017-03-30T21:26:36.143",
    "MetadataState": "Created",
    "ForceRefreshState": "CompletedWithFailures",
    "LastForceRefreshRequestTime": null,
    "LastForceRefreshStartTime": "2017-11-17T20:55:43.32",
    "LastForceRefreshEndTime": "2017-11-17T20:56:09.377"
  },
  "WriteDeleteLog": true,
  "CreationTime": "2017-03-30T04:09:15.08",
  "ActivationTime": "2017-03-30T21:26:34.19",
  "UpdateTime": "2017-03-30T21:26:20.643",
  "DestinationSchemaName": "dbo"
}

 

JSON Schema: 

{
  "type": "object",
  "properties": {
    "Id": {
      "type": "string"
    },
    "Version": {
      "type": "string"
    },
    "State": {
      "type": "integer"
    },
    "LastModified": {
      "type": [
        "string",
        "null"
      ]
    },
    "Name": {
      "type": "string"
    },
    "OrganizationId": {
      "type": "string"
    },
    "Entities": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "Type": {
            "type": "string"
          },
          "Settings": {
            "type": "object",
            "properties": {}
          },
          "Status": {
            "type": "object",
            "properties": {
              "Type": {
                "type": "string"
              },
              "ProfileId": {
                "type": "string"
              },
              "TotalNotifications": {
                "type": "integer"
              },
              "SuccessNotifications": {
                "type": "integer"
              },
              "FailureNotifications": {
                "type": "integer"
              },
              "LastExportDate": {
                "type": [
                  "string",
                  "null"
                ]
              },
              "LastExportStatus": {
                "type": [
                  "string",
                  "null"
                ]
              },
              "EntityMetadataState": {
                "type": [
                  "string",
                  "null"
                ]
              },
              "ExpectedInitialSyncNotifications": {
                "type": "integer"
              },
              "InitialSyncState": {
                "type": [
                  "string",
                  "null"
                ]
              },
              "VersionNumber": {
                "type": "integer"
              },
              "ChangeTime": {
                "type": [
                  "string",
                  "null"
                ]
              },
              "MetadataVersionNumber": {
                "type": "integer"
              },
              "MetadataChangeTime": {
                "type": [
                  "string",
                  "null"
                ]
              }
            }
          },
          "InitialSyncMetadataCreatedTime": {
            "type": [
              "string",
              "null"
            ]
          },
          "AdditionTime": {},
          "InitialSyncDataCompletedTime": {
            "type": [
              "string",
              "null"
            ]
          },
          "LastDeltaSyncMetadataTime": {
            "type": [
              "string",
              "null"
            ]
          },
          "LastDeltaSyncDataTime": {
            "type": [
              "string",
              "null"
            ]
          },
          "InitialSyncProcessCompleteTime": {
            "type": [
              "string",
              "null"
            ]
          }
        },
        "required": [
          "Type",
          "Settings",
          "Status",
          "InitialSyncMetadataCreatedTime",
          "AdditionTime",
          "InitialSyncDataCompletedTime",
          "LastDeltaSyncMetadataTime",
          "LastDeltaSyncDataTime",
          "InitialSyncProcessCompleteTime"
        ]
      }
    },
    "DestinationType": {
      "type": "integer"
    },
    "DestinationKeyVaultUri": {
      "type": "string"
    },
    "DestinationPrefix": {
      "type": "string"
    },
    "RetryPolicy": {
      "type": "object",
      "properties": {
        "MaxRetryCount": {
          "type": "integer"
        },
        "IntervalInSeconds": {
          "type": "integer"
        },
        "Backoff": {
          "type": "integer"
        }
      }
    },
    "Status": {
      "type": "object",
      "properties": {
        "ExportStatus": {
          "type": [
            "string",
            "null"
          ]
        },
        "InitialSyncState": {
          "type": [
            "string",
            "null"
          ]
        },
        "TotalNotifications": {
          "type": "integer"
        },
        "SuccessNotifications": {
          "type": "integer"
        },
        "FailureNotifications": {
          "type": "integer"
        },
        "LastExportDate": {
          "type": [
            "string",
            "null"
          ]
        },
        "LastModifiedOn": {
          "type": [
            "string",
            "null"
          ]
        },
        "MetadataState": {
          "type": [
            "string",
            "null"
          ]
        },
        "ForceRefreshState": {
          "type": [
            "string",
            "null"
          ]
        },
        "LastForceRefreshRequestTime": {},
        "LastForceRefreshStartTime": {
          "type": [
            "string",
            "null"
          ]
        },
        "LastForceRefreshEndTime": {
          "type": [
            "string",
            "null"
          ]
        }
      }
    },
    "WriteDeleteLog": {
      "type": "boolean"
    },
    "CreationTime": {
      "type": "string"
    },
    "ActivationTime": {
      "type": [
        "string",
        "null"
      ]
    },
    "UpdateTime": {
      "type": [
        "string",
        "null"
      ]
    },
    "DestinationSchemaName": {
      "type": [
        "string",
        "null"
      ]
    }
  }
}

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: JSON Parse Output: How to access properites from Child Object

Hi @seanmcn,

 

I have made a test on my side, please refer to screenshot below to create the flow:

Capture.PNG

Capture.PNG

 

The Array in the Value field of "Initial variable"as below:

[
  {
    "Type": "account",
    "Settings": {},
    "Status": {
      "Type": "account",
      "ProfileId": "898bbc15-5a77-4a8f-821a-9949fe3f226d",
      "TotalNotifications": 1647531,
      "SuccessNotifications": 1647531,
      "FailureNotifications": 0,
      "LastExportDate": "2018-12-01T11:51:02.6910357Z",
      "LastExportStatus": "Success",
      "EntityMetadataState": "Created",
      "ExpectedInitialSyncNotifications": 0,
      "InitialSyncState": "Completed",
      "VersionNumber": 28326699,
      "ChangeTime": "2018-12-01T11:50:56",
      "MetadataVersionNumber": 28283951,
      "MetadataChangeTime": "2018-11-30T14:17:03"
    },
    "InitialSyncMetadataCreatedTime": "2017-03-30T04:10:13.913",
    "AdditionTime": null,
    "InitialSyncDataCompletedTime": "2017-03-30T05:00:50.763",
    "LastDeltaSyncMetadataTime": "2018-11-30T14:17:05.86",
    "LastDeltaSyncDataTime": "2018-12-01T11:51:02.69",
    "InitialSyncProcessCompleteTime": "2017-03-30T04:17:12.327"
  },
  {
    "Type": "new_a",
    "Settings": {},
    "Status": {
      "Type": "new_a",
      "ProfileId": "898bbc15-5a77-4a8f-821a-9949fe3f226d",
      "TotalNotifications": 33,
      "SuccessNotifications": 29,
      "FailureNotifications": 4,
      "LastExportDate": "2018-06-13T20:51:33.1164211Z",
      "LastExportStatus": "Success",
      "EntityMetadataState": "Created",
      "ExpectedInitialSyncNotifications": 0,
      "InitialSyncState": "CompletedWithFailures",
      "VersionNumber": 0,
      "ChangeTime": "0001-01-01T00:00:00",
      "MetadataVersionNumber": 22345017,
      "MetadataChangeTime": "2018-06-13T20:51:27"
    },
    "InitialSyncMetadataCreatedTime": "2017-03-30T21:26:55.673",
    "AdditionTime": null,
    "InitialSyncDataCompletedTime": null,
    "LastDeltaSyncMetadataTime": "2018-06-13T20:51:33.1",
    "LastDeltaSyncDataTime": null,
    "InitialSyncProcessCompleteTime": "2017-03-30T21:27:00.157"
  }
]

The expression of LastExportDate as below:

items('Apply_to_each')['Status']['LastExportDate']

 

The expression of LastExportStatus as below:

items('Apply_to_each')['Status']['LastExportStatus']

 

The flow would run successfully as below:

Capture.PNG

 

Best regards,

Alice

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: JSON Parse Output: How to access properites from Child Object

Hi @seanmcn,

 

I have made a test on my side, please refer to screenshot below to create the flow:

Capture.PNG

Capture.PNG

 

The Array in the Value field of "Initial variable"as below:

[
  {
    "Type": "account",
    "Settings": {},
    "Status": {
      "Type": "account",
      "ProfileId": "898bbc15-5a77-4a8f-821a-9949fe3f226d",
      "TotalNotifications": 1647531,
      "SuccessNotifications": 1647531,
      "FailureNotifications": 0,
      "LastExportDate": "2018-12-01T11:51:02.6910357Z",
      "LastExportStatus": "Success",
      "EntityMetadataState": "Created",
      "ExpectedInitialSyncNotifications": 0,
      "InitialSyncState": "Completed",
      "VersionNumber": 28326699,
      "ChangeTime": "2018-12-01T11:50:56",
      "MetadataVersionNumber": 28283951,
      "MetadataChangeTime": "2018-11-30T14:17:03"
    },
    "InitialSyncMetadataCreatedTime": "2017-03-30T04:10:13.913",
    "AdditionTime": null,
    "InitialSyncDataCompletedTime": "2017-03-30T05:00:50.763",
    "LastDeltaSyncMetadataTime": "2018-11-30T14:17:05.86",
    "LastDeltaSyncDataTime": "2018-12-01T11:51:02.69",
    "InitialSyncProcessCompleteTime": "2017-03-30T04:17:12.327"
  },
  {
    "Type": "new_a",
    "Settings": {},
    "Status": {
      "Type": "new_a",
      "ProfileId": "898bbc15-5a77-4a8f-821a-9949fe3f226d",
      "TotalNotifications": 33,
      "SuccessNotifications": 29,
      "FailureNotifications": 4,
      "LastExportDate": "2018-06-13T20:51:33.1164211Z",
      "LastExportStatus": "Success",
      "EntityMetadataState": "Created",
      "ExpectedInitialSyncNotifications": 0,
      "InitialSyncState": "CompletedWithFailures",
      "VersionNumber": 0,
      "ChangeTime": "0001-01-01T00:00:00",
      "MetadataVersionNumber": 22345017,
      "MetadataChangeTime": "2018-06-13T20:51:27"
    },
    "InitialSyncMetadataCreatedTime": "2017-03-30T21:26:55.673",
    "AdditionTime": null,
    "InitialSyncDataCompletedTime": null,
    "LastDeltaSyncMetadataTime": "2018-06-13T20:51:33.1",
    "LastDeltaSyncDataTime": null,
    "InitialSyncProcessCompleteTime": "2017-03-30T21:27:00.157"
  }
]

The expression of LastExportDate as below:

items('Apply_to_each')['Status']['LastExportDate']

 

The expression of LastExportStatus as below:

items('Apply_to_each')['Status']['LastExportStatus']

 

The flow would run successfully as below:

Capture.PNG

 

Best regards,

Alice

View solution in original post

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (4,628)