Logic Apps - Sort json

Question

Logic Apps - Sort json

Andoni Aguirre 45

Hello,

I need to sort this json by the "exp" key, ascending. How can I do this? Standard sort() function does not do the job User's image

MayankBargali-MSFT 70,941 Reputation points Moderator

2023-09-21T08:13:34.6266667+00:00

Hi @Anonymous

Following up to see if my answer and comment helps. Feel free to get back to me if you need any assistance.

Please "Accept Answer" if the answer is helpful so that it can help others in the community.

Accepted answer

1 additional answer

Your answer

MayankBargali-MSFT 70,941 Reputation points Moderator

2023-09-21T08:13:34.6266667+00:00

Hi @Anonymous

Following up to see if my answer and comment helps. Feel free to get back to me if you need any assistance.

Please "Accept Answer" if the answer is helpful so that it can help others in the community.

Answer 1

MayankBargali-MSFT 70,941 Moderator

@Anonymous Thanks for reaching out. Sort method would have helped in scenario when your property is not the nested JSON object.

For example, if your json would be below and you want to sort it with the property ex then you would have used the

sort(body('Parse_JSON'),'exp') but this doesn't work with the nested json object properties.

[
  {
    "ex": "2023-09-20",
    "attributes": {
      "enabled": true,
      "exp": "2023-09-20"
    },
    "id": "1",
    "tags": {
      "owner": "test"
    }
  },
  {
   "ex": "2023-09-19",
    "attributes": {
      "enabled": true,
      "exp": "2023-09-19"
    },
    "id": "2",
    "tags": {
      "owner": "test"
    }
  },
  {
  "ex": "2023-09-18",
    "attributes": {
      "enabled": true,
      "exp": "2023-09-18"
    },
    "id": "3",
    "tags": {
      "owner": "test"
    }
  }
]

The workaround would be leveraging the liquid template or use the inline code to sort it as per your need.

Let me know if you have any queries or concerns.

Andoni Aguirre 45 Reputation points

2023-09-19T11:54:34.5766667+00:00

Hello @MayankBargali-MSFT ! Yes, I realized that sort method is not valid for this case...what do you mean by "leveraging the liquid template"? There is no straightforward way to do this?

MayankBargali-MSFT 70,941 Moderator

@Anonymous If you are familiar with liquid templates then you can transform the JSON to JSON object as per your business needs and you can refer to this document. If you are more familiar with the node JS code then you can leverage the inline code. For example, I have used the below inline code to sort the JSON object. Please review the document on the usage part and if there is any limitation.

const data = workflowContext.actions.Compose.outputs return data.sort((a, b) => new Date(a.attributes.exp) - new Date(b.attributes.exp));

Input:

[
  {
    "attributes": {
      "enabled": true,
      "exp": "2023-09-20"
    },
    "id": "1",
    "tags": {
      "owner": "test"
    }
  },
  {
    "attributes": {
      "enabled": true,
      "exp": "2023-09-19"
    },
    "id": "2",
    "tags": {
      "owner": "test"
    }
  },
  {
    "attributes": {
      "enabled": true,
      "exp": "2023-09-18"
    },
    "id": "3",
    "tags": {
      "owner": "test"
    }
  }
]

Output:

[
  {
    "attributes": {
      "enabled": true,
      "exp": "2023-09-18"
    },
    "id": "3",
    "tags": {
      "owner": "test"
    }
  },
  {
    "attributes": {
      "enabled": true,
      "exp": "2023-09-19"
    },
    "id": "2",
    "tags": {
      "owner": "test"
    }
  },
  {
    "attributes": {
      "enabled": true,
      "exp": "2023-09-20"
    },
    "id": "1",
    "tags": {
      "owner": "test"
    }
  }
]

User's image

Andoni Aguirre 45 Reputation points

2023-09-21T08:36:34.37+00:00

Hello @MayankBargali-MSFT

Thanks a lot for the help! Maybe I did not mention but my logic app is a consumption based app, which runs once a day at 07:00 CEST for checking if there are secrets about to expire in one Azure Key Vault.

The solution you propose looks awesome! However, it requires to use an integration account ($$$$$), which costs 255€/month (this is way too much).

A colleague helped me find a solution for it in here (although it's extremely slow): https://www.tachytelic.net/2022/07/power-automate-sort-json-array/?amp

Do you know any other way for sorting this, having costs into consideration? Thanks
Andoni Aguirre 45 Reputation points

2023-09-21T08:41:23.6566667+00:00

Dear @MayankBargali-MSFT ,

Thanks a lot for your help!

Maybe I did not mention it but my logic apps is a simple consumption based logic app that checks if there are secrets about to expire in one azure key vault, sending email if any. It is executed once a day.

The solution you propose seems to require an integration account, which adds considerable costs (255€/month), so I can't go with that

FYI, a colleague told me to use this as reference (although it's for power automate, it can be coded in Logic Apps as well. However, it makes the flow pretty slow): https://www.tachytelic.net/2022/07/power-automate-sort-json-array/?amp

Do you know any other solution that does not require to use integration account?
MayankBargali-MSFT 70,941 Reputation points Moderator

2023-09-21T09:08:48.6933333+00:00

@Anonymous Yes using different action you need to write your own custom logic with multiple action and individual action would cost you. The alternative way would be offloading it to consumption azure function which has per grant execution per month for a subscription and Price per million executions would also cost a way less. Now in your workflow you can call azure function (you can use the same nodejs code that I shared) which will do sorting for your requirement. You can compare the different cost of azure function and logic app per action execution which would be cheaper for your requirement of sorting the JSON object and function would be way faster than using multiple action within the logic app. On a quick look I can see there are around 15 actions at least and it be loop that adds up more depending on the number of elements. You can review the pricing details for logic app and function to valid which would be better in cost effective for your sorting.
Andoni Aguirre 45 Reputation points

2023-09-21T09:12:31.35+00:00

@MayankBargali-MSFT I have just thought about using Azure Function for sorting the json 10 minutes ago (now I need to learn how to code Azure Function). My idea is to have a python coded azure function (I am not familiar with js) that is called by the Logic Apps (with that json as body). This python code would sort the json and return it sorted to the Logic App
MayankBargali-MSFT 70,941 Reputation points Moderator

2023-09-21T09:16:04.08+00:00

@Anonymous Yes that should be good. One more thing to add that Integration does have the free tier which you could leverage for small things but there would be no SLA and for more details you can refer to this.
Andoni Aguirre 45 Reputation points

2023-09-21T09:25:23.0266667+00:00

@MayankBargali-MSFT amazing, you helped me a lot! Hope you have a great day, I will mark your post as solution

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Logic Apps - Sort json

1 additional answer

Your answer