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

Parse JSON Data

Hi,

 

I've created a Dynamics CRM Webhook which Posts to a Flow HTTP Request, as described here. The data i receive I looks like the the example below. For me to retreive the "fullname" value, I would use the following JSON Path: $.InputParameters.[0].value.Attributes[2].value. How do i achive this in Flow?

 

{
    "BusinessUnitId": "e2b9dd85-e89e-e711-8122-000d3aa2331c",
    "CorrelationId": "b374239d-4233-41a9-8b17-a86cb4f737b5",
    "Depth": 1,
    "InitiatingUserId": "75c2dd85-e89e-e711-8122-000d3aa2331c",
    "InputParameters": [{
        "key": "Target",
        "value": {
            "__type": "Entity:http:\/\/schemas.microsoft.com\/xrm\/2011\/Contracts",
            "Attributes": [{
                "key": "firstname",
                "value": "James"
            }, {
                "key": "contactid",
                "value": "6d81597f-0f9f-e711-8122-000d3aa2331c"
            }, {
                "key": "fullname",
                "value": "James Glynn (sample)"
            }, {
                "key": "yomifullname",
                "value": "James Glynn (sample)"
            }, {
                "key": "modifiedon",
                "value": "\/Date(1506384247000)\/"
            }, {
                "key": "modifiedby",
                "value": {
                    "__type": "EntityReference:http:\/\/schemas.microsoft.com\/xrm\/2011\/Contracts",
                    "Id": "75c2dd85-e89e-e711-8122-000d3aa2331c",
                    "KeyAttributes": [],
                    "LogicalName": "systemuser",
                    "Name": null,
                    "RowVersion": null
                }
            }, {
                "key": "modifiedonbehalfby",
                "value": null
            }],
            "EntityState": null,
            "FormattedValues": [],
            "Id": "6d81597f-0f9f-e711-8122-000d3aa2331c",
            "KeyAttributes": [],
            "LogicalName": "contact",
            "RelatedEntities": [],
            "RowVersion": null
        }
    }],
    "IsExecutingOffline": false,
    "IsInTransaction": false,
    "IsOfflinePlayback": false,
    "IsolationMode": 1,
    "MessageName": "Update",
    "Mode": 1,
    "OperationCreatedOn": "\/Date(1506409448000-0700)\/",
    "OperationId": "4af10637-4ea2-e711-8122-000d3aa2331c",
    "OrganizationId": "4ef5b371-e89e-e711-8122-000d3aa2331c",
    "OrganizationName": "OrgName",
    "OutputParameters": [],
    "OwningExtension": {
        "Id": "75417616-4ea2-e711-8122-000d3aa2331c",
        "KeyAttributes": [],
        "LogicalName": "sdkmessageprocessingstep",
        "Name": null,
        "RowVersion": null
    },
    "PostEntityImages": [{
        "key": "AsynchronousStepPrimaryName",
        "value": {
            "Attributes": [{
                "key": "fullname",
                "value": "James Glynn (sample)"
            }, {
                "key": "contactid",
                "value": "6d81597f-0f9f-e711-8122-000d3aa2331c"
            }],
            "EntityState": null,
            "FormattedValues": [],
            "Id": "6d81597f-0f9f-e711-8122-000d3aa2331c",
            "KeyAttributes": [],
            "LogicalName": "contact",
            "RelatedEntities": [],
            "RowVersion": null
        }
    }],
    "PreEntityImages": [],
    "PrimaryEntityId": "6d81597f-0f9f-e711-8122-000d3aa2331c",
    "PrimaryEntityName": "contact",
    "RequestId": null,
    "SecondaryEntityName": "none",
    "SharedVariables": [],
    "Stage": 40,
    "UserId": "75c2dd85-e89e-e711-8122-000d3aa2331c"
}

 

1 ACCEPTED SOLUTION

Accepted Solutions
JoeO
Level: Powered On

Re: Parse JSON Data

Just for my own reference, here is my solution.

I was having an issue because Flow  read the timestamp value as a string, so I needed to add a step to convert to an integer. I have the following steps (3 compose steps).

 

  1. I get a data from Dynamics CRM, the date value shows as: "value": "/Date(1553029200000)/"
  2. The first compose step finds the value using the Json path, and strips everything besides the numbers. The compose looks like this:
    replace(replace(triggerBody()['InputParameters'][0]['value']['Attributes'][0]['value'],'/Date(',''),')/','')
  3. The output is “1553029200000” as a string, so the next compose step is to convert it to a integer which looks like this:
    int(outputs('Strip_Date_1'))
  4. Finally, I convert the unix time to human readable eastern time
    convertFromUtc(addSeconds('1970-01-01',div(outputs('Convert_Timestamp_to_Int'),1000),'yyyy-MM-ddTHH:mmZ'),'Eastern Standard Time', 'G')

 

View solution in original post

3 REPLIES 3
JoeO
Level: Powered On

Re: Parse JSON Data

To answer my own question, its simple (shown in the expression below). The part i could not figure out of the life of me was that each branch in the path needs to be in brackets, and character values in single quotes.

 

The part im still unable to figure out is to convert a date value which shows up as "Date(1553569200000)/". I can easily convert this unix timestamp, but how do I strip the timestamp from the rest so that my final output is “1553569200000”?

 

concat(triggerBody()['InputParameters'][0]['value']['Attributes'][0]['value'])

 
JoeO
Level: Powered On

Re: Parse JSON Data

I can use a substring expression like the one below, but i need to hard code the length, which isnt always the same, i guess ill need to use two replace functions. Anyone has any other better ideas?

 

substring(triggerBody()['InputParameters'][0]['value']['Attributes'][0]['value'],6,13)

 

replace(triggerBody()['InputParameters'][0]['value']['Attributes'][0]['value'],'/Date(','')

Use the above output, and then run

replace(outputs('Strip_Date_1'),')/','')

JoeO
Level: Powered On

Re: Parse JSON Data

Just for my own reference, here is my solution.

I was having an issue because Flow  read the timestamp value as a string, so I needed to add a step to convert to an integer. I have the following steps (3 compose steps).

 

  1. I get a data from Dynamics CRM, the date value shows as: "value": "/Date(1553029200000)/"
  2. The first compose step finds the value using the Json path, and strips everything besides the numbers. The compose looks like this:
    replace(replace(triggerBody()['InputParameters'][0]['value']['Attributes'][0]['value'],'/Date(',''),')/','')
  3. The output is “1553029200000” as a string, so the next compose step is to convert it to a integer which looks like this:
    int(outputs('Strip_Date_1'))
  4. Finally, I convert the unix time to human readable eastern time
    convertFromUtc(addSeconds('1970-01-01',div(outputs('Convert_Timestamp_to_Int'),1000),'yyyy-MM-ddTHH:mmZ'),'Eastern Standard Time', 'G')

 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

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 Kudoed Authors (Last 30 Days)
Users online (3,971)