Replace words in JSON object based on compose data

[Kav7]

Hello,

I have a JSON object that I need to replace the name of some attributes, a sample JSON object is:

{
  "Name": "Kav 338",
  "Email": "kav@hotmail.com",
  "License": "Mailbox",
  "Office": "Sydney",
  "SubmittersWorkEmailwillBeUsedToVerifyYou": "kav@gmail.com",
}

I want to use another JSON object as the source for filtering, as an example:

{
  "SubmittersWorkEmailwillBeUsedToVerifyYou": "Your email",
  "Office": "Location"
{

So basically I want to replace 'SubmittersWorkEmailwillBeUsedToVerifyYou' with 'Your email' and 'Office' with 'Location', in the first JSON object (the name, not the actual value).

Tried an 'Apply to each' with the second JSON object above as input, with the replace() function, but not having much luck... Got errors about self referencing, then an error about data types.

[CFernandes]

Hey @Kav7 

Have you tried using https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#... 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

[Kav7]

This appears to change the value of the JSON rather than its name? How would I integrate it into a loop so that it does it on each 'replace' in my second JSON object?

[grantjenkins]

Did you have an array of objects you want to do this for, or just a single object?

Also, will the following Filter always just contain those two properties or others? And if the property doesn't exist in the Filter, would it just use the original property name?

{
  "SubmittersWorkEmailwillBeUsedToVerifyYou": "Your email",
  "Office": "Location"
{

I've built a sample flow that "might" be what you're after. Full flow below. I'll go into each of the actions.

Data is a Compose that contains the base JSON data (for this example).

[
    {
        "Name": "Abc 123",
        "Email": "abc@hotmail.com",
        "License": "Mailbox",
        "Office": "Brisbane",
        "SubmittersWorkEmailwillBeUsedToVerifyYou": "abc@gmail.com",
    },
    {
        "Name": "Kav 338",
        "Email": "kav@hotmail.com",
        "License": "Mailbox",
        "Office": "Sydney",
        "SubmittersWorkEmailwillBeUsedToVerifyYou": "kav@gmail.com",
    }
]

Filter is a Compose that contains the Property Filters.

{
    "SubmittersWorkEmailwillBeUsedToVerifyYou": "Your email",
    "Office": "Location"
}

Select takes in the output from Data and converts it to proper JSON using the json expression.

json(outputs('Data'))

The Values in this example are:

item()?['Name']
item()?['Email']
item()?['License']
item()?['Office']
item()?['SubmittersWorkEmailwillBeUsedToVerifyYou']

And the Keys using the Filter object are:

if(empty(outputs('Filter')?['Name']), 'Name', outputs('Filter')?['Name'])
if(empty(outputs('Filter')?['Email']), 'Email', outputs('Filter')?['Email'])
if(empty(outputs('Filter')?['License']), 'License', outputs('Filter')?['License'])
if(empty(outputs('Filter')?['Office']), 'Office', outputs('Filter')?['Office'])
if(empty(outputs('Filter')?['SubmittersWorkEmailwillBeUsedToVerifyYou']), 'SubmittersWorkEmailwillBeUsedToVerifyYou', outputs('Filter')?['SubmittersWorkEmailwillBeUsedToVerifyYou'])

The output from the Select would be:

[
  {
    "Name": "Abc 123",
    "Email": "abc@hotmail.com",
    "License": "Mailbox",
    "Location": "Brisbane",
    "Your email": "abc@gmail.com"
  },
  {
    "Name": "Kav 338",
    "Email": "kav@hotmail.com",
    "License": "Mailbox",
    "Location": "Sydney",
    "Your email": "kav@gmail.com"
  }
]

[Kav7]

Did you have an array of objects you want to do this for, or just a single object?

Nope just a single JSON object that needs words swapped out, though the properties in the JSON input can differ and change.

Also, will the following Filter always just contain those two properties or others? And if the property doesn't exist in the Filter, would it just use the original property name?

Over time I would be adding properties to the filter, so thats why I needed an 'apply to each' loop applied to the filter object and yes if the property doesnt exist in the input JSON it just leaves it as-is.

Because of the dynamic nature, the select portion doesnt really achieve what I am after as its static in relation to the input JSON.

I thought it would be a simple JSON -> Loop through 'filter' properties -> If 'filter' property exists in JSON, use replace() to swap out the data -> Update JSON object

Problem is when selecting JSON properties, it seems you have to specify the name in the expression as the reference which gives you the 'value', but its the name itself that I need as well as the value. Maybe the 'filter' JSON object isn't the appropriate object format for what I am trying to achieve, but its effectively just a lookup table.

What I attempted was something like this:

[grantjenkins]

@Kav7 Are you able to change the filter to a slightly different format to what you currently have? So, keep the initial JSON data as it is.

But change the filter data from:

{
    "Submitter": "Your email",
    "Office": "Location"
}

To something like the following instead:

[
  {
    "from": "Submitter",
    "to": "Your email"
  },
  {
    "from": "Office",
    "to": "Location"
  }
]

If you can change the filter object to that format, then I think I've got a working solution. If not, then I'll change my flow slightly using XPath which should still work fine.

[grantjenkins · Accepted solution]

I've got it working using your original Filter JSON. Had to use XPath to get the actual property names.

Below is the full flow. I'll go into each of the actions.

Data (Compose) contains your initial JSON object.

{
  "Name": "Kav 338",
  "Email": "kav@hotmail.com",
  "License": "Mailbox",
  "Office": "Sydney",
  "Submitter": "kav@gmail.com"
}

jsonData is a string variable that's initially set to the initial JSON data.

Filter (Compose) contains your filter data.

{
    "Submitter": "Your email",
    "Office": "Location"
}

XML (Compose) converts your Filter JSON data to XML so we can use XPath. It also adds a root element so it's valid XML. The expression used here is:

xml(json(concat('{"root": ', outputs('Filter'), '}')))

Apply to each iterates over each of the items in the Filter object using XPath. The expression used here is:

xpath(outputs('XML'), '//root/*')

Compose uses some more XPath to see if the element names match the filter item, and if so, replace it. The expression used here is:

replace(variables('jsonData'), concat('"', xpath(items('Apply_to_each'), 'name(//*)'), '":'), concat('"', xpath(items('Apply_to_each'), 'string(//*/text())'), '":'))

We then set the jsonData variable to the output of the Compose so eventually it will contain the final output.

Finally, outside (after) the Apply to each, we get use another Compose (Final) to convert the text stored in our jsonData variable to proper JSON using the json expression.

json(variables('jsonData'))

And that's it - you should have your replaced property names in JSON format.

[Kav7]

That worked, amazing thank you!

I noticed one odd thing though, if I have a space in the filter object replacement like this:

{
    "Submitter": "Your email",
    "Office": "Big City"
}

In the output "Office" gets replaced with "Big_x0020_City".

Any idea why?

[grantjenkins]

I'm not sure. When I have that I get the following output:

What do you get with "Your email"?

[Kav7]

Managed to fix/workaround it by using this expression for the 'attribute' up the chain: replace(xpath(item(), 'name(//*)'), '_x0020_',' ')

Seems to do the job 🙂

I had inserted your flow above into a pre-existing flow (other ones you helped with!) and I think the issue stemmed from some processing after the above flow. This looks odd for example:

But doesn't matter, this works, thanks again 🙂