Expression in field in Dynamics used to get JSON from another field

(0) Share

Report

Posted on by dsammied

I have a field in Dataverse on a form with JSON Content. The JSON content is correct. I have another field in Dataverse on a form that states the expression to help get some of the content from the JSON when using power automate.

I am using Power automate to try and retrieve the specific JSON Content using the Expression in the field as above.

but when the flow runs the result is empty

What am I doing wrong please TIA

All responses (6)

Answers (1)

Dengliang Li Microsoft Employee on at

Like (0)

Report

Expression in field in Dynamics used to get JSON from another field

Hi,

Has the issue been solved?
Have there been any updates? Please feel free to ask me if you have any questions!
If it has been resolved, can you mark the response as an answer?
Dengliang Li Microsoft Employee on at

Like (0)

Report

Expression in field in Dynamics used to get JSON from another field

Hi,

The expression for the Compose step is shown below:

body('Parse_JSON')?[json(variables('JSON Expression'))[0]]?[json(variables('JSON Expression'))[1]][0]?[json(variables('JSON Expression'))[2]]

Since the DateToBeExtended property value is an array, you need to add an element indexer [0] to get into the array. Then you can get the objects within the array.

Best Regards,
Dengliang Li
SC-03092122-0 2 on at

Like (0)

Report

Expression in field in Dynamics used to get JSON from another field

Hi there,

What about when you have JSON that is nested. For example, in the below JSON how would you find Deadline in the JSON Expression.

I see you can get to DateToBeExtended with: body('Parse_JSON')?[json(variables('JSON Expression'))[0]]?[json(variables('JSON Expression'))[1]]

but it doesn't work for trying to retrieve 'Deadline' from the JSON when you try:

JSON Expression in the field on the form: ['DateOfTime','DateToBeExtended','Deadline']

Compose Expression in flow: body('Parse_JSON')?[json(variables('JSON Expression'))[0]]?[json(variables('JSON Expression'))[1]]?[json(variables('JSON Expression'))[2]]

JSON Body:

{

"DateOfTime": {

"Detail": "Detail",

"DateToBeExtended": [

{

"Detail": "Detail",

"Deadline": "Request something",

"CurrentDate": "15/01/2024"

}

],

"Reason": "A very good reason"

}

}
Dengliang Li Microsoft Employee on at

Like (0)

Report

Expression in field in Dynamics used to get JSON from another field

Hi,

It's achievable.
This is the JSON content to be parsed.

{"ExtensionOfWork":{

"DateToBeExtended":{

"Date":"2024-01-16T07:52:01Z"

}

}}

The expression used is the same as yours.

body('Parse_JSON')?[json(variables('JSON Expression'))[0]]?[json(variables('JSON Expression'))[1]]?[json(variables('JSON Expression'))[2]]

The flow runs successfully and gets the value of the Date property.

In summary, the expression is flexibly designed based on the JSON content.

Best Regards,
Dengliang Li
Dsammie0 7 on at

Like (0)

Report

Expression in field in Dynamics used to get JSON from another field

Thank you Dengliang Li that has worked!

Would this also work with something like the below in the JSON Expression field? Can I keep extending the number of values?

body('Parse_JSON')?[json(variables('JSON Expression'))[0]]?[json(variables('JSON Expression'))[1]]?[json(variables('JSON Expression'))[2]]

Many thanks :)
Verified answer

Dengliang Li Microsoft Employee on at

Like (1)

Report

Expression in field in Dynamics used to get JSON from another field

Hi,

I encountered the same issue as you in my testing.

You can try the following steps.

I replace the JSON expression with an array. This array is of string type and can subsequently be parsed into a JSON array.

The array in the JSON Content field is then obtained by the following expression.

body('Parse_JSON')?[json(variables('JSON Expression'))[0]]?[json(variables('JSON Expression'))[1]]

json(variables('JSON Expression'))[0] Represents the first array element FormDetails in the JSON expression field.
json(variables('JSON Expression'))[1] Represents the first array element AppNumbers in the JSON expression field.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more.
If you have further questions, please feel free to contact me .

Best Regards,
Dengliang Li