Issue with Output file in JSON format

RajR

Hi,

I am trying to create a control file in JSON format to accompany a data file within a workflow. Requirement is to generate the control file in the below format.

{
"files" : [ {
"name" : "EX_RATES_20190528124700.dat",
"path" : "/QUANTUM/EX_RATES",
"rowCount" : 110
} ]
}

I am creating the name, path & rowcount in a formula within the same workflow and then writing this to a output data tool in JSON format. This is what I have in the table that's going into the output data tool.

But, when the workflow is run, the control file output comes like below.

[
    {
        "files": "[ {",
        "name": "EX_RATES_20190710145039.dat",
        "path": "/QUANTUM/EX_RATES",
        "rowCount": 110
    }
] 

Any help on where I am going wrong and how to get the control file output in the format required?

Thanks,

RajR

danilang

Hi @RajR 

The Alteryx .json output doesn't do any kind of nesting of data.  It just outputs your fields and value as name:value strings.  If you want a more complex structure, use the JSON builder tool.    

This workflow takes your input record + one extra prefix field and manipulates the columns and values to get it into this format

This is the format that the JSON builder tool uses to create properly formated nested JSON strings.  The result of this is the following JSON string

{"files":[{"name":"EX_RATES_20190528124700.dat","path":"/QUANTUM/EX_RATES","rowcount":110}]}

Aside from splitting onto multiple lines, this is exactly the format that you require

Dan

JSON.yxmd