mvzip with fillnull for converting json to table

[pasokkum]

Hi,

I want to convert a json file to table format.. JSON structure is
"Settings": {
"Employee": [
{
"name": "EF",
"Domain": "JAVA",
},
{
"name": "CD",
"Domain": ".NET",
},
{
"name": "AB",
"Domain": "SQL",
} ]
"Student": [
{
"name": "EF",
"Domain": "CSE",
},
{
"name": "AB",
"Domain": "IT",
} ]
}
When i use the following query,

index=myindex | rename Settings.Employee{}.name as nameEmployee Settings.Employee{}.Domain as DomainEmployee Settings.Student{}.name as nameStudent Settings.Student{}.Domain as DomainStudent | eval x = mvzip(mvzip(mvzip(nameEmployee,DomainEmployee,"?"),nameStudent,"?"),DomainStudent,"?") | mvexpand x | eval x = split(x,"?") | eval nameEmployee=mvindex(x,0) | eval DomainEmployee =mvindex(x,1) | eval nameStudent =mvindex(x,2) | eval DomainStudent =mvindex(x,3) | table nameEmployee DomainEmployee nameStudent DomainStudent

I am getting the following table,

I am expecting a table like this with null values also

Pls help..! Thanks in advance!

[somesoni2]

Try something like this

index=myindex | rename Settings.Student{}.* as *Student Settings.Employee{}.* as *Employee | eval emp=mvzip(nameEmployee,DomainEmployee,"#") | eval std=mvzip(nameStudent,DomainStudent,"#") | table emp std | mvexpand emp | rex field=emp "(?<nameEmployee>.+)#(?<DomainEmployee>.+)" | mvexpand std | rex field=std "(?<nameStudent>.+)#(?<DomainStudent>.+)" | fields - emp std | foreach *Student [eval <<FIELD>>=if(nameStudent=nameEmployee,<<FIELD>>,null())] | stats values(*Student) as *Student by nameEmployee DomainEmployee