Json key value mapping

[vivekkumarkk]

I have my splunk Jason in below format

{ [-]
delete_me: True
vendor: Dbruzy
name: Rahul
date: [ [-]
10-jan-2022
30-dec-2022
]
count_target: [ [-]
1700
300
]
site: India
type: Sales
}

I am looking for a query to get output like this:

Vendor	Name	Date	Count_Target	Site	Type
Dbruzy	Rahul	10-jan-2022	1700	India	Sales
Dbruzy	Rahul	30-dec-2022	300	India	Sales

But I am getting as below:

Vendor	Name	Date	Count_Target	Site	Type
Dbruzy	Rahul	10-jan-2022 30-dec-2022	1700 300	India	Sales
Dbruzy	Rahul	10-jan-2022 30-dec-2022	1700 300	India	Sales

Query I am using:

my index
| rename count_target{} as target
| rename Date{} as voltage
| spath input=voltage path=voltage output=someOtherField
| spath input=someOtherField
| foreach voltage*
[ eval voltage=mvappend(voltage, '<<FIELD>>')] | spath input=target path=target output=someOtherField1
| spath input=someOtherField1
| foreach target*
[ eval target=mvappend(target, '<<FIELD>>')] | mvexpand target| mvexpand voltage
| stats values(voltage) as Date values(target) as Count_Target by Vendor, Name,Site,Type

Can you please help?

[ITWhisperer]

I don't see how your SPL matches the example json you shared. Please can you share the raw json in a code block </>

[vivekkumarkk]

Thanks for the response.

My SPL may not be right. You can find the raw data:

{"delete_me": "True", "vendor": "Dbruzy", "site": "India", "type": "Sales", "name": "Rahul", "date": ["10-jan-2022", "30-dec-2022"], "count_target": ["1700", "300"]}

SPL I corrected in below way:

my index
| rename count_target{} as target
| rename Date{} as voltage
| foreach voltage*
[ eval voltage=mvappend(voltage, '<<FIELD>>')]
| foreach target*
[ eval target=mvappend(target, '<<FIELD>>')] | mvexpand target| mvexpand voltage
| stats values(voltage) as Date values(target) as Count_Target by Vendor, Name,Site,Type

[ITWhisperer · Accepted solution]

You are using foreach incorrectly - you only have one field that matches voltage* and target* - foreach doesn't iterate through the multivalues in the field - you could use mvmap to do that but I am not sure that would help you in this instance. From your SPL, it looks like you already have the fields extracted from the json, but I have used spath in this runanywhere example

| makeresults 
| eval _raw="{\"delete_me\": \"True\", \"vendor\": \"Dbruzy\", \"site\": \"India\", \"type\": \"Sales\", \"name\": \"Rahul\", \"date\": [\"10-jan-2022\", \"30-dec-2022\"], \"count_target\": [\"1700\", \"300\"]}"
| table _raw
| spath



| rename count_target{} as target
| rename date{} as voltage
| mvexpand target
| streamstats count as row by name site type vendor
| eval voltage=mvindex(voltage,row-1)
| fields - row

[vivekkumarkk]

Thank You @ITWhisperer 

😌