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"
}
]
}

JSON_TEST.yxmd

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.

JSON_TEST.yxmd