I have a Power Automate flow that gets a JSON file from a SharePoint folder (when dropped into it) and I want to be able to read its contents and extract a key/value. The issue I am having is that it crashes in the second step, Get File Content, with the error

Encountered internal server error. The tracking Id is '3bc1890c-1932-4a2e-af14-6e5cca1534be'.

I realized that it has to do with the JSON file's syntax. When I ran the flow with the below JSON file

{
    "products": {
      "GF": [
        {
          "product_name": "GF",
          "remediation": {
            "type": "package",
            "packages": [
              {
                "service": "av",
                "service_name": "AV",
                "description": "Detects Prestige ransomware payloads",
                "kill_chain": {
                  "step": "Delivery"
                },
                "link": "https://www.GF.com/updates/antivirus?version=90.06850",
                "minimum_version": "90.06850"
              }
            ]
          },
          "detection": {
            "virus": [
              "W32/Filecoder.OMM!tr.ransom"
            ],
            "virusid": [
              10108353
            ]
          }
        }
      ]
    },
    "title": "Prestige Ransomware",
    "sub_title": "Impacting organizations in Ukraine and Poland",
    "link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/",
    "cve_list": [
      "TBA"
    ],
    "description": "Researchers at Microsoft Threat Intelligence Center (MSTIC) have identified evidence of a novel ransomware campaign targeting organizations in the transportation and logistics industries in Ukraine and Poland. According to the report, the new ransomware labels itself with a ransom note of “Prestige ranusomeware”.",
    "tag": "Prestige Ransomware",
    "event_handler_desc": "",
    "m": 851548,
    "additional_resources": [
      {
        "Source": "Microsoft Security Blog",
        "Link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/"
      }
    ],
    "ob_url": "https://www.GF.com/ob-alert/prestige-ransomware"
  }

the PA workflow executes successfully. However, when I try with the same PA workflow with the JSON file below (the required one)

[
  {
    "products": {
      "GF": [
        {
          "product_name": "GF",
          "remediation": {
            "type": "package",
            "packages": [
              {
                "service": "av",
                "service_name": "AV",
                "description": "Detects Prestige ransomware payloads",
                "kill_chain": {
                  "step": "Delivery"
                },
                "link": "https://www.GF.com/updates/antivirus?version=90.06850",
                "minimum_version": "90.06850"
              }
            ]
          },
          "detection": {
            "virus": [
              "W32/Filecoder.OMM!tr.ransom"
            ],
            "virusid": [
              10108353
            ]
          }
        }
      ]
    },
    "title": "Prestige Ransomware",
    "sub_title": "Impacting organizations in Ukraine and Poland",
    "link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/",
    "cve_list": [
      "TBA"
    ],
    "description": "Researchers at Microsoft Threat Intelligence Center (MSTIC) have identified evidence of a novel ransomware campaign targeting organizations in the transportation and logistics industries in Ukraine and Poland. According to the report, the new ransomware labels itself with a ransom note of “Prestige ranusomeware”.",
    "tag": "Prestige Ransomware",
    "event_handler_desc": "",
    "m": 851548,
    "additional_resources": [
      {
        "Source": "Microsoft Security Blog",
        "Link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/"
      }
    ],
    "ob_url": "https://www.GF.com/ob-alert/prestige-ransomware"
  }
]

the workflow crashes with the error I posted in the beginning of my post. It seems that the initial and ending [ ] make the workflow step of Get File Content to crash.

The problem is that this is the actual JSON syntax I need to work with. Any idea or suggestion would ne very valuable, because I have been working on it for the last couple of days and I am stuck BIG TIME!

The PA workflow is the below

Thank you in advance, Nikos

CodePudding user response：

If the content of the file can't be inferred (because the file doesn't have an extension) that action returns a base64 string.

When you rename the file with an extension of .txt it infers the content as text and therefore works.

Rather than rename the file, which if you want to make it perfect will require elements of pain depending on the approach you take, you can simply decode the base64 string in the $content field by using an expression like this ...

decodeBase64(body('Get_file_content')?['$content'])

... within the Content field of the Parse JSON action but within the Expression section, not the Dynamic content section.

That should give you what you want.

Result

CodePudding user response：

I am posting some screen shots that can probably help clarifying what you are looking for. Below is the result from the test run:

The power automate design flow is the below:

I hope these images help. Thank you

Hi all (again),

After the successful execution of the flow, all the way to Parse JSON step (following @Skin solution), I ran a couple of tests with some JSON files that I need to use and the Parse JSON step gives out errors regarding "missing" required properties.

Basically, the JSON file's arrays do not always have exactly the same elements (properties). For example (below) the element "minimun_version" does not always appear under the element "link"

How can I Parse such a JSON file successfully? Thank you