topic Re: convert a nested json into table in Dashboards & Visualizations

How to convert a nested json into table?

anooshac — Thu, 08 Jun 2023 12:45:24 GMT

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.

Re: convert a nested json into table

ITWhisperer — Thu, 08 Jun 2023 06:04:48 GMT

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

Re: convert a nested json into table

anooshac — Thu, 08 Jun 2023 06:25:06 GMT

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

Re: convert a nested json into table

ITWhisperer — Thu, 08 Jun 2023 06:27:31 GMT

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

Re: convert a nested json into table

gcusello — Thu, 08 Jun 2023 06:34:14 GMT

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

Re: convert a nested json into table

anooshac — Thu, 08 Jun 2023 08:36:02 GMT

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

Re: convert a nested json into table

ITWhisperer — Thu, 08 Jun 2023 08:35:00 GMT

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

Re: convert a nested json into table

anooshac — Thu, 08 Jun 2023 08:35:08 GMT

@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.

Re: convert a nested json into table

ITWhisperer — Thu, 08 Jun 2023 08:39:27 GMT

What is the search that is giving you these results?

Re: convert a nested json into table

gcusello — Thu, 08 Jun 2023 08:51:26 GMT

Hi @anooshac ,

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

Ciao.

Giuseppe

Re: convert a nested json into table

anooshac — Thu, 08 Jun 2023 09:00:06 GMT

@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

Re: convert a nested json into table

anooshac — Thu, 08 Jun 2023 09:02:32 GMT

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

Re: convert a nested json into table

ITWhisperer — Thu, 08 Jun 2023 09:16:51 GMT

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?

Re: convert a nested json into table

anooshac — Thu, 08 Jun 2023 10:17:12 GMT

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".

Re: convert a nested json into table

ITWhisperer — Thu, 08 Jun 2023 10:32:06 GMT

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

Re: convert a nested json into table

anooshac — Mon, 12 Jun 2023 07:06:34 GMT

@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?

Re: convert a nested json into table

ITWhisperer — Mon, 12 Jun 2023 07:28:20 GMT

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?

Re: convert a nested json into table

anooshac — Mon, 12 Jun 2023 08:00:58 GMT

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

Re: convert a nested json into table

ITWhisperer — Mon, 12 Jun 2023 09:36:43 GMT

| 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

Re: convert a nested json into table

anooshac — Wed, 14 Jun 2023 10:46:05 GMT

Thank you so much @ITWhisperer , It is working.