Writing JSON Files - Wrong Syntax

[Polina]

Hi,

I'm trying to write out a JSON file that has a hierarchical structure, but the created file has odd/wrong syntax. 

Here is a simple example that illustrates my problem.

1. Four rows of data are created via Text Input tool in appropriate format

JSON_Name	JSON_ValueString
record.0.category	A
record.0.location	Paris
record.1.category	B
record.1.location	London

2. Using JSON Build tool, these records are converted into a single string, that describe objects and value arrays in JS format

JSON

{"record":[{"category":"A","location":"Paris"},{"category":"B","location":"London"}]}

3. The resulting string, with the column title JSON is written as JSONExample.json file with standard Alteryx configuration (Unicode 8 by default)

4. When opening JSONExample.json in the notepad all quotes are replaced by \", as highlighted below

[
    {
        "JSON": "{\"record\":[{\"category\":\"A\",\"location\":\"Paris\"},{\"category\":\"B\",\"location\":\"London\"}]}"
    }
]

Has anyone come across a similar issue?

I must be missing some steps in preparing my data, so any help/advice is welcome.

[NeilR · Accepted solution]

You're formatting as JSON twice - once in the JSON Build tool, and once in the Output tool. To keep the Output tool from messing up the formatting you've already applied, change the type to delimited (with a null - \0 - delimiter) and uncheck the First Row Contains Field Names box, as attached.

[Polina]

Hi Neil,

Thank you for the tip - it works perfect now and the output json file looks as expected!

Do you know of a smarter method to prepare the data to actually use the JSON output type in Alteryx?

For instance, is there a way to convert the following original data into an accepted format for JSON output:

category	location
A	Paris
B	London

[NeilR]

If you output the above table to JSON with the Output tool without any further preparation you'll get the following:

[
    {
        "category": "A",
        "location": "Paris"
    },
    {
        "category": "B",
        "location": "London"
    }
]

That looks pretty good to me, but if you want finer control (like adding the "records" key) then you should probably stick with the JSON Build tool method.

[JP_SDAK]

I had to dig around in community, and some of the posts that referenced community articles, to figure out that using both the JSON builder and output with JSON format is doing double duty.

Spoiler

[alexnajm]

Edit: whoops, I meant to put this on the challenge!

Thank goodness for the Community!

Spoiler