cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JoeO Helper II
Helper II

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 Helper II
Helper II

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 Helper II
Helper II

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 Helper II
Helper II

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 Helper II
Helper II

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
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

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

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Users online (6,648)