Convert Text input to JSON with JSON build tool

[ViktorJohansson]

Hi,

Im having problem to create my JSON output according to below structure. I have tried tou use JSON build tool but my Array and object keeps getting wrong. 

Anyone that can help?

Text Input:

contactDetails sendMail Key Value
test1@actic.se True 900295 PRIVATE
test1@actic.se True 900296 ACTIVE
test1@actic.se True 900297 1
test1@actic.se True 900298 1
test1@actic.se True 900304 SE
test1@actic.se True 900305 Stockholm Ulriksdal
test2@actic.se True 900295 PRIVATE
test2@actic.se True 900295 PRIVATE
test2@actic.se True 900296 ACTIVE
test2@actic.se True 900296 ACTIVE
test2@actic.se True 900297 1
test2@actic.se True 900297 1
test2@actic.se True 900298 1
test2@actic.se True 900298 1
test2@actic.se True 900304 SE
test2@actic.se True 900304 SE
test2@actic.se True 900305 Stockholm Ulriksdal
test2@actic.se True 900305 Stockholm Ulriksdal

How I want it to look like in JSON:sholud look like in JSON:

{

"contactDetails": "test1@actic.se",

"sendMail": "true",

"backgroundData": [

{

"Key": "900296",

"Value": "ACTIVE"

},

{

"Key": "900295",

"Value": "PRIVATE"

},

{

"Key": "900297",

"Value": "1"

},

{

"Key": "900298",

"Value": "1"

},

{

"Key": "900304",

"Value": "SE"

},

{

"Key": "900305",

"Value": "Stockholm Ulriksdal"
}
]
}

[Luke_C]

Hi @ViktorJohansson 

When working with the JSON build tool, my first step is to take the expected JSON output and run it through a JSON parse tool. That will allow us to reverse engineer the format:

 

Then from there, we can build the workflow:

• I used a tile tool to index the data by contact details & background data
• Then I transposed the background data and created the JSON field name dynamically using a formula tool
• I also processed the contact details & send email field separately, as they are listed just once in the JSON
• I unioned the data together and sorted, then built the JSON as expected.

 

[ViktorJohansson]

Hi @Luke_C  and thanks for the respons.

I've tried to use your workflow and it works fine but when Im doing a POST with the download tool Im getting an output wtih a lot of backslashes. 

And I also found out that our JSON structure needs start with a text "respondents" before the first object since I want to sending more objects in our JSON.

I did add the new structure to your example and also the part where I want to POST my data but with out backslashes. 

Thanks for your help!

[Luke_C]

Hi @ViktorJohansson 

The slashes are seen in the download data, which is interesting. I'd suggest trying the same in postman and seeing if the same issue occurs. I'm not sure that this is an issue with Alteryx, but rather that the api is returning the JSON String rather than the JSON response (see this link https://magento.stackexchange.com/questions/333410/return-json-response-instead-of-json-string) 

[ViktorJohansson]

Hi Luke,

When Im POST data with Postman It wokrs fine. The output data that I posted in the the comment in my JSON_TEST_NEW dataflow is how it looks like when I POST it to an endpoint my IT department built for testing.The only thing this endpoint do is to log the incoming request and return the exact same thing.It's clearly visible in the log from the server that the incoming request include these backslashes.

So it seems to be a problem within Alteryx or what do you think?

Thanks for your help!