cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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
Highlighted
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'])

 
Highlighted
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'),')/','')

Highlighted
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
Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Users online (9,637)