Logic App - Create CSV table from JSON output

Beefcake picture Beefcake · Feb 22, 2018 · Viewed 10.4k times · Source

I've created a Runbook which starts or shutsdown VMs and then spits the result in a JSON output. I'm having trouble trying to figure out how to take that output and use it in a 'Create CSV Table' within my Logic App.

Output (which is exactly how it comes into the Logic App):

[
    {
        'VM':  'MyVM2',
        'Success':  true,
        'PSComputerName':  'localhost',
        'PSShowComputerName':  true,
        'PSSourceJobInstanceId':  '286eeccb-c7f6-4afd-a734-7f4e837ffdac'
    },
    {
        'VM':  'MyVM1',
        'Success':  true,
        'PSComputerName':  'localhost',
        'PSShowComputerName':  true,
        'PSSourceJobInstanceId':  '286eeccb-c7f6-4afd-a734-7f4e837ffdac'
    }
]

My Logic App flow: enter image description here

However I am prompted with the following error when it runs because the ‘Create CSV Table’ doesn’t like the input: enter image description here

I’m sure it’s just some sort of formatting that needs to be changed, but I am unsure how to do it. I'm new to learning code, and as far as I can tell the output is an Array of Objects, but the error is asking for an Array. Perhaps some sort of limitation?

I'm aware of using Pars JSON, however the results will not always be the same. Sometimes I will have 2 results in the output, or sometimes I will have 20 results. Therefore if I define the schema in Parse JSON for only 2 results, it will omit the rest because they are not defined.

Answer

Tom Sun - MSFT picture Tom Sun - MSFT · Feb 26, 2018

I'm aware of using Pars JSON, however the results will not always be the same. Sometimes I will have 2 results in the output, or sometimes I will have 20 results. Therefore if I define the schema in Parse JSON for only 2 results, it will omit the rest because they are not defined.

You also could use the Pars JSON, if the peperties of the each record are fixed. Please have a try to use the following Schema. I test it on my side, it works correctly.

{
    "items": {
        "properties": {
            "PSComputerName": {
                "type": "string"
            },
            "PSShowComputerName": {
                "type": "boolean"
            },
            "PSSourceJobInstanceId": {
                "type": "string"
            },
            "Success": {
                "type": "boolean"
            },
            "VM": {
                "type": "string"
            }
        },
        "required": [
            "VM",
            "Success",
            "PSComputerName",
            "PSShowComputerName",
            "PSSourceJobInstanceId"
        ],
        "type": "object"
    },
    "type": "array"
}

enter image description here

enter image description here