Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Writing JSON Files - Wrong Syntax

Polina
5 - Atom

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.

 

 

6 REPLIES 6
NeilR
Alteryx Alumni (Retired)

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
5 - Atom

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:

 

 categorylocation 
 A Paris
 B London
NeilR
Alteryx Alumni (Retired)

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.

RichoBsJ
11 - Bolide
 
JP_SDAK
8 - Asteroid

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
JP_SDAK_1-1610222803558.pngJP_SDAK_2-1610222819402.png

 

alexnajm
16 - Nebula
16 - Nebula

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

 

Thank goodness for the Community!

Spoiler
Challenge 114.PNG

 

Labels