Convert to JSON and map to new JSON object in Alteryx

Question

I am using Alteryx to take an Excel file and convert to JSON. The JSON output I'm getting looks different to what I was expecting and also the object starts with "JSON": which I don't want to happen and I would also like to know how/which components I would use to map fields to specific JSON fields instead of key value pairs if I need to later in the flow.

I have attached my sample workflow and excel which are: Excel screenshot

Alteryx test flow

JSON output I am seeing:

[
    {
        "JSON": "{\"email\":\"[email protected]\",\"startdate\":\"2020-12-01\",\"isEnabled\":\"0\",\"status\":\"active\"}"
    },
    {
        "JSON": "{\"email\":\"[email protected]\",\"startdate\":\"2020-12-02\",\"isEnabled\":\"1\",\"status\":\"active\"}"
    }
]

What I expected:

[{
        "email": "[email protected]",
        "startdate": "2020-12-01",
        "isEnabled": "0",
        "status": "active"
    },
    {
        "email": "[email protected]",
        "startdate": "2020-12-02",
        "isEnabled": "1",
        "status": "active"
    }
]

Also, what component would I use if I wanted to map the structure above to another JSON structure similar this one:

 [{
    "name":"MyName",
    "accounType":"array",
    "contactDetails":{
        "email":"[email protected]",
        "startDate":"2020-12-01"
        }
    }
} ]

Thanks

Answer 1

In the workflow that you have built, you are essentially creating the JSON twice. The JSON Build creates the JSON structure, so if you then want to output it, select your file to output and then change the dropdown to csv with delimiter \0 and no headers.

However, try putting an output straight after your Excel file and output to JSON, the Output Tool will build the JSON for you.

In answer to your second question, build the JSON for Contact Details first as a field (remember to rename JSON to contactDetails). Then build from there with one of the above options.