community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

JSON in Input Data - property names as columns, property values as rows - how?

Hi,

I am new to Alteryx.

Suppose, i have JSON file with following content:

[{
    "CustomerId":"11111111",
    "CountryCode":"SE"
},
{
    "CustomerId":"22222222",
    "CountryCode":"FI"
}]

I would like to use it as input data.

I expect it to be de-serialized into:

CustomerIdCountryCode
111111111SE
22222222FI

 

Instead, i have:

10.CustomerId11111111
20.CountryCodeSE
31.CustomerId2222222
41.CountryCodeFI

Is there any way to configure the way JSON is parsed in Alteryx?

Alteryx
Alteryx

Hi @DikAlex777

 

Essentially, the iInput tool will parse the JSON field into JSON Names and JSON Values – JSON Name is a listing of all the columns in the dataset (each occurrence) and JSON Value has the row value corresponding to each.

 

To get them in standard tabulated format, you’ll want to cross tab with JSON Name being the Header and JSON Value being the values.

 

I have attached sample workflow (Alteryx 10.6) to get the desired format.

 

2017-02-07_17-37-12.png

 

Best,

 

Jordan Barker

Solutions Consultant

Hi Jordan,

Thank you, that works!

But, the problem is that i cannot reconstruct the JSON back to original form. I changed your workflow a bit, and use text input, then JSON Parse, then as you suggest via Pivot to table format.

Then, i need to add some columns to the table, and then for each row construct JSON in form "ColumnName":"RecordValue", but it does not work.

Even simple combo JSON Parse -> JSON Build gives an error: JSON Build (20): An array can't be at the top level.

I have not found any description about this error, just a warning from JSON Build that this is experimental tool.

 

I tried Output data tool to save to JSON file, and it works as expected, but then, how i use the data from the file in my workflow?

The point is that i need to add few parameters to customer id and send them to a web service as POST body in JSON form.

one of the options is to use Formula and do simple concatenation of strings, but i just wanted to do "proper" serialization.

Example is attached. in the example, only Output Data tool gives original JSON, if you check the file content.

The first JSON Build thrown aforementioned error, and the final JSON Build gives "{"CountryCode":"SE","ID":"1111111","CountryCode":"FI","ID":"222222"}" which is not the original one, but close.

 

Also, though it is not directly related to this question, but how one serializes data to XML in Alteryx? I have found only XML parse tool.

Alteryx
Alteryx

Hi @DikAlex777

 

I've take a look internally and found this workflow which shows you how to parse the JSON and then build it back.

 

The reason the array error was appearing was because we needed a single field rather than a array before the JSON build tool. More details can be found within the module.

 

Workflow attached (10.6)

 

Best,

 

Jordan Barker

Solutions Consultant

Labels