How to convert a nested json into table?

[anooshac]

Hi all, i have a json file like this

{
	"NUM": "#7",
	"TIME": "May 23, 2022, 09:24:40 PM",
	"STATUS": "SUCCESS",
	"DURATION": "2 hours, 13 minutes",
	"URL": "abc.com",
	"COMPONENTS": [{
			"NAME": "abc",
			"Tasks": [{
				"ITEM": [{
						"ITEM_ID": "2782508",
						"FILE": "file1"
					},
					{
						WORKITEM_ID": "2782508",
						"FILE": "file2"
					},
					{
						"ITEM_ID": "2782508",
						"FILE": "file1"
					},
					{
						"ITEM_ID": "2782508",
						"FILE": "file3"
					}
				]

			}]
		},
		{
			"NAME": "xyz",
			"tasks": [{
				"ITEM": [{
					"ITEM_ID": "2811478",
					"FILE": "file2"
				}]

			}]
		}
	]
}

how can i create a table with columns "num time status duration component_name itemid file". How can i make all the values come in different rows not together.

[ITWhisperer]

Try something like this

| foreach NUM TIME STATUS DURATION
    [| spath <<FIELD>>]
| spath COMPONENTS{} output=components
| mvexpand components
| spath input=components NAME
| spath input=components Tasks{}.ITEM{} output=items
| mvexpand items
| spath input=items
| fields - components items _raw

[anooshac]

 tried this and im not getting a table, only events are showing.

[gcusello]

Hi @anooshac,

please try something like this:

<your_search>
| spath
| table NUM TIME STATUS DURATION COMPONENTS{}.NAME COMPONENTS{}.NAME.TASK{}.ITEMID COMPONENTS.NAME.TASK{}.FILE

I'm not sure about the last three field names, please check them.

Ciao.

Giuseppe

[anooshac]

HI @gcusello ,This gives data in a single row. I want the data in different rows.

[gcusello]

Hi @anooshac ,

you asked for a table, this is a table, could you give a sample of the report youìd like?

Ciao.

Giuseppe

[anooshac]

Hi @gcusello , this is how the resutls should come. And also i have individual JSON file for each "NUM" .

NUM	STATUS	TIME	DURATION	NAME	ITEM_ID	FILE
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file1
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file2
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file1
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file3
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	xyz	2811478	file2

[ITWhisperer]

Here is a runanywhere example demonstrating it work (with corrections to your supplied data)

| makeresults
| fields - _time
| eval _raw="{
	\"NUM\": \"#7\",
	\"TIME\": \"May 23, 2022, 09:24:40 PM\",
	\"STATUS\": \"SUCCESS\",
	\"DURATION\": \"2 hours, 13 minutes\",
	\"URL\": \"abc.com\",
	\"COMPONENTS\": [{
			\"NAME\": \"abc\",
			\"Tasks\": [{
				\"ITEM\": [{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file1\"
					},
					{
						\"WORKITEM_ID\": \"2782508\",
						\"FILE\": \"file2\"
					},
					{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file1\"
					},
					{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file3\"
					}
				]

			}]
		},
		{
			\"NAME\": \"xyz\",
			\"Tasks\": [{
				\"ITEM\": [{
					\"ITEM_ID\": \"2811478\",
					\"FILE\": \"file2\"
				}]

			}]
		}
	]
}"
| foreach NUM TIME STATUS DURATION
    [| spath <<FIELD>>]
| spath COMPONENTS{} output=components
| mvexpand components
| spath input=components NAME
| spath input=components Tasks{}.ITEM{} output=items
| mvexpand items
| spath input=items
| fields - components items _raw

[anooshac]

@ITWhispererthere are individual file for all the "NUM". Will that be a problem for this?

I am getting as no results found. Only events are shown.

[ITWhisperer]

What is the search that is giving you these results?

[anooshac]

@ITWhisperer,The same search which yo have provided gives me this results.

the desired output should be , sorry for not mentioning clearly.

NUM	STATUS	TIME	DURATION	NAME	ITEM_ID	FILE
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file1
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file2
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file1
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file3
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	xyz	2811478	file2

[ITWhisperer]

The solution relies on the _raw field holding the JSON string (as shown on the runanywhere example).

The most likely reason for the solution not giving the expected result is that the _raw is not in JSON format, or, there is a mistake in the JSON (as there was in your pasted example).

Please can you share your actual raw events (with sensitive data obfuscated of course) in a code block </> as you did originally?

[anooshac]

hi @ITWhisperer 

{
	"NUM": "#7",
	"TIME": "May 23, 2022, 09:24:40 PM",
	"STATUS": "SUCCESS",
	"DURATION": "2 hours, 13 minutes",
	"URL": "abc.com",
	"COMPONENTS": [{
			"NAME": "abc",
			"Tasks": [{
				"ITEM": [{
						"ITEM_ID": "2782508",
						"FILE": "file1"
					},
					{
						"ITEM_ID ": "2782508 ",
						"FILE": "file2"
					},
					{
						"ITEM_ID": "2782508",
						"FILE": "file1"
					},
					{
						"ITEM_ID": "2782508",
						"FILE": "file3"
					}
				]

			}]
		},
		{
			"NAME": "xyz",
			"tasks": [{
				"ITEM": [{
					"ITEM_ID": "2811478",
					"FILE": "file2"
				}]

			}]
		}
	]
}

this is the correct JSON file and the table required is,

NUM	STATUS	TIME	DURATION	NAME	ITEM_ID	FILE
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file1
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file2
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file1
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	abc	2782508	file3
#7	SUCCESS	May 23, 2022, 09:24:40 PM	2 hours, 13 minutes	xyz	2811478	file2

and also i have individual JSON file for "NUM".

[ITWhisperer]

This is a runanywhere example based on your "correct" JSON

| makeresults
| fields - _time
| eval _raw="{
	\"NUM\": \"#7\",
	\"TIME\": \"May 23, 2022, 09:24:40 PM\",
	\"STATUS\": \"SUCCESS\",
	\"DURATION\": \"2 hours, 13 minutes\",
	\"URL\": \"abc.com\",
	\"COMPONENTS\": [{
			\"NAME\": \"abc\",
			\"Tasks\": [{
				\"ITEM\": [{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file1\"
					},
					{
						\"ITEM_ID \": \"2782508 \",
						\"FILE\": \"file2\"
					},
					{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file1\"
					},
					{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file3\"
					}
				]

			}]
		},
		{
			\"NAME\": \"xyz\",
			\"tasks\": [{
				\"ITEM\": [{
					\"ITEM_ID\": \"2811478\",
					\"FILE\": \"file2\"
				}]

			}]
		}
	]
}"
| foreach NUM TIME STATUS DURATION
    [| spath <<FIELD>>]
| spath COMPONENTS{} output=components
| mvexpand components
| spath input=components NAME
| spath input=components Tasks{}.ITEM{} output=items
| mvexpand items
| spath input=items
| fields - components items _raw

However, I assume that there are a couple of typos, e.g. tasks should be Tasks and there shouldn't be a space after one of the ITEM_ID field names.

Having corrected these, gives this runanywhere example

| makeresults
| fields - _time
| eval _raw="{
	\"NUM\": \"#7\",
	\"TIME\": \"May 23, 2022, 09:24:40 PM\",
	\"STATUS\": \"SUCCESS\",
	\"DURATION\": \"2 hours, 13 minutes\",
	\"URL\": \"abc.com\",
	\"COMPONENTS\": [{
			\"NAME\": \"abc\",
			\"Tasks\": [{
				\"ITEM\": [{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file1\"
					},
					{
						\"ITEM_ID\": \"2782508 \",
						\"FILE\": \"file2\"
					},
					{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file1\"
					},
					{
						\"ITEM_ID\": \"2782508\",
						\"FILE\": \"file3\"
					}
				]

			}]
		},
		{
			\"NAME\": \"xyz\",
			\"Tasks\": [{
				\"ITEM\": [{
					\"ITEM_ID\": \"2811478\",
					\"FILE\": \"file2\"
				}]

			}]
		}
	]
}"
| foreach NUM TIME STATUS DURATION
    [| spath <<FIELD>>]
| spath COMPONENTS{} output=components
| mvexpand components
| spath input=components NAME
| spath input=components Tasks{}.ITEM{} output=items
| mvexpand items
| spath input=items
| fields - components items _raw

Which gives this result

[anooshac]

@ITWhisperer, Thank you so much it is working..

I was also creating another table for the data,

NAME	ITEM_ID	FILE
abc	2782508	file1
abc	2782508	file2
abc	2782508	file1
abc	2782508	file3
xyz	2811478	file2

using the below query,

myindex...| mvexpand COMPONENTS{}.NAME
|mvexpand COMPONENTS{}.Tasks{}.ITEM{}
|rename COMPONENTS{}.NAMEas NAME COMPONENTS{}.Tasks{}.ITEM{}.ITEM_ID as ITEM_ID COMPONENTS{}.Tasks{}.ITEM{}.FILE as FILE | eval x=mvzip(ITEM_ID,FILE)|mvexpand x| eval ITEM_ID=mvindex(split(x,","),0)| eval FILE=mvindex(split(x,","),1)
| table NAME, ITEM_ID FILE

instead of 5 entries i am getting 10 entries. I am not sure what is the mistake here. Can you please tell if there is any?

[ITWhisperer]

By mvexpand NAME, you have doubled your events, when you mvexpand x, you get 5 times the events. 2 * 5 = 10

If you have something that is working, why are you trying to break it?

[anooshac]

@ITWhisperer, But if i don't do that the table comes as empty. Is there any other way to do this?

[ITWhisperer · Accepted solution]

| spath COMPONENTS{} output=components
| mvexpand components
| spath input=components NAME
| spath input=components Tasks{}.ITEM{} output=items
| mvexpand items
| spath input=items
| fields - components items _raw
| table NAME ITEM_ID FILE

[anooshac]

Thank you so much @ITWhisperer , It is working.

[ITWhisperer]

If this solution doesn't give you the output you were expecting. please show us what the desired output should look like.