How to beautify the JSON output from a workflow

[stijngoossens]

The goal with this workflow is a readable file in JSON format. The file should be understood by colleagues with less experienced with the JSON format. 

The following workflow will create a *.json output file which works fine and is readable for other programs, but not for humans. I kept the example simple, but it can get messy pretty quickly formatting a lot of values on different levels. The following string is received from test_output.json:

{"vehicle":"Plane","value":"10.000","first_use":"01-01-2020","last_use":"31-12-2020","part_type":"123019, 1230120, 12301, 1993, 10299","lines":[{"net_weight":"1000","total_cost":"3200000","packaging_quantity":"135","quantity_unit":"pieces","line_number":"1269157"}]}

The desired (manually beautified) output is however:

{

"vehicle": "Plane",

"value":"10.000",

"first_use":"01-01-2020",

"last_use":"31-12-2020",

"part_type":"123019, 1230120, 12301, 1993, 10299",

"lines": [

{

"net_weight":"1000",

"total_cost":"3200000",

"packaging_quantity":"135",

"quantity_unit":"pieces",

"line_number":"1269157"

}

]

}

Note that this can somewhat be achieved with a RegEx function in Alteryx by replacing the { to {\n\t and replacing the "," to ",\n". But this will only give the following output:

{
"vehicle":"Plane",
"value":"10.000",
"first_use":"01-01-2020",
"last_use":"31-12-2020",
"part_type":"123019, 1230120, 12301, 1993, 10299",
"lines":[{
"net_weight":"1000",
"total_cost":"3200000",
"packaging_quantity":"135",
"quantity_unit":"pieces",
"line_number":"1269157"}]}

This if of course readable, but still not perfect if we want to see the different levels in the JSON. This could probably be achieved using some more RegEx functions and adding some more columns with more values implicating different levels, but this could get messy pretty quickly. 

Note that the attached *.txt files are a replacement for the original *.json.

So the question is: is there any way to automatically beautify the json format in a function such that the keys and levels are automatically displayed in a nice and readable way.

Thanks in advance for answering the question.

[CharlieS · Accepted solution]

Hi @stijngoossens 

It looks like you were on the right track by replacing bracket characters with added newlines. I kept that going and added a few steps and I think I've got something close.

- I started by continuing your bracket replacement that add newlines to the other bracket characters.

- I split these rows into records so I could add a [Depth] field to each line. 

- This [Depth] field is based on counts of open brackets (increases depth) or close brackets (reduces depth). 

- I used this depth field to dynamically pad the left of each line with a single character, then replaced that character with a set of spaces (since the pad only works with single characters)

- Finally I concatenated these lines into a single field with the Summarize tool and I think it looks pretty good. 

These rules may not work in every scenario, and the replacement character might need to change, but it shows that something like this is possible. Check out the attached workflow and let me know what you think!

[stijngoossens]

Hi CharlieS,

Thanks for you reply. This is a nice solution to the problem. I do think however that there should be an option within Output Data to make the JSON output more readable. As long as this option is not available, I will be using your solution as long as the beautifier is not added. 

Thanks!