JSON Structure Build

[mclane20]

Hi All, 

I have reviewed some of the documents and articles on using the Json tool. I cant seem to get it work exactly how I need it. Please let me know if you can help. Below is the sample Data set and then also the json structure it needs to become. 

Data Set:

host	source	index	sourcetype	event-type	event-process	event-action	event-pod
Alteryx	BP	BP1	BP2	Stuck	Process A	Restart Process	Team A
Alteryx	BP	BP1	BP2	Terminated	Process B	Restart VDI	Team B
Alteryx	BP	BP1	BP2	Queue	Process C	Add more	Team C

host, index, source, and source type will be the same value only things that change are the events. This payload will be generated and sent via an api call to a logging system. 

Desired Json Output: 

{
    "host": "Alteryx",
    "source": "BP",
    "index": "BP1",
    "sourcetype": "BP2",
    "event":
   [
   {
       "type": "Stuck", 
       "process": "Process A",
       "action": "Restart Process",
       "pod": "Team A"
   },
   { 
      "type": "Terminated",
      "process": "Process B",
      "action": "Restart VDI",
      "pod": "Team B" 
   },
   {  
      "type": "Queue",
      "process": "Process C",
      "action": "Add more",
      "pod": "Team C" 
   }
   ]
}

 Thanks so much for your help and feedback. 

[grossal]

Hi @mclane20,

your desired Output looks very odd to me. I'd understand if you want the "general information" in a higher hierarchy than the rest, but only having the top level information in the first element seems strange to me.

Alex

[mclane20]

Hi I updated the Json payload I was missing some brackets, apologies. My intent for this structure is to be able to send multiple events in one payload. the basic info one needs to be there once while the event data could have many records. 

[grossal]

I am not aware of an Alteryx way to do this. I have tested the JSON build, JSON build v2 and JSON Create from the CReW Macros, but none of them worked.

However, we still have the Python tool to do the magic.

Output:

Beautified:

You can remove the outer [] if you want (you didn't have them in your Desired Output.

What happens inside the Python tool is quiet simple:

Workflow attached. Let me know what you think.

Best

Alex

[danilang]

Hi @mclane20 

Here's a way you can accomplish the same thing without resorting to python, using the CReWs JSON Create macro

This workflow separates the inner event columns and parses them into a single JSON sting and appends this to the outer level.  This is passed again through  JSON Create to create an object with a nested array.  After some clean-up to remove the escaped characters, you get 

{
	"host":"Alteryx",
	"source":"BP",
	"index":"BP1",
	"sourcetype":"BP2",
	"Event":
	[
	{
		"event-type":"Stuck",
		"event-process":"Process A",
		"event-action":"Restart Process",
		"event-pod":"Team A"
	},
	{
		"event-type":"Terminated",
		"event-process":"Process B",
		"event-action":"Restart VDI",
		"event-pod":"Team B"
	},
	{
		"event-type":"Queue",
		"event-process":"Process C",
		"event-action":"Add more",
		"event-pod":"Team C"
	}
	]
}

Note: The output is a actually single string with no line breaks.  I added those manually. 

Dan

[Alc2020]

Hi @danilang,

I need the same workflow as you did.  I downloaded yours but I'm getting an error on the parsing tool (please see attached).  How do I fix that?  Thanks in advance.

[daw44]

You need to install the popular CREW macros. They are available at http://www.chaosreignswithin.com/p/macros.html.