topic Re: how to split the json array into multiple new events in Getting Data In

how to split the json array into multiple new events

wood1986 — Wed, 30 Oct 2013 11:18:55 GMT

I have a json like this format

{
  "id":"123412341234",
  "actions": [ 
    {
      "type":"a",
      "status":"b",
      "amount": 1,
      "time_updated": "2013-10-14T11:00"
    },
    {
      "type":"c",
      "status":"d",
      "amount": 1,
      "time_updated": "2013-10-14T12:00"
    }
  ]
}

I want to know how to split the array into multiple new events like

time_updated     id           type status amount
2013-10-14T11:00 123412341234 a    b      1
2013-10-14T12:00 123412341234 c    d      2

Thanks!

Re: how to split the json array into multiple new events

MuS — Wed, 30 Oct 2013 12:25:30 GMT

Hi wood1986,

have a look at the spath search command, its purpose is to have a straightforward means for extracting information from structured data formats like XML and JSON.

hope this helps ...

cheers, MuS

Re: how to split the json array into multiple new events

sowings — Wed, 30 Oct 2013 13:28:28 GMT

Note, you might have to use spath to get multi-value fields, then mvexpand to get events from each distinct set.

Re: how to split the json array into multiple new events

tcoq — Mon, 06 Jan 2014 17:57:02 GMT

examples will pretty nice

Re: how to split the json array into multiple new events

MuS — Tue, 07 Jan 2014 06:58:28 GMT

examples you want....so you probably did not read the docs then 😉 there are examples on how to use spath on XML and JSON -> http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/Spath#Examples

Re: how to split the json array into multiple new events

prokopowicz — Tue, 22 Jul 2014 16:37:03 GMT

This example does not address the question. The example describes how to turn an event that has a field with multiple values into multiple events. It does not describe how to turn an event with a JSON array into multiple events. The difference is this:
{ var : val1, var : val2, var : val3 }

vs this
var : [val1, val2, val3].
The example covers the first, the question concerns the second. Does anyone know how to turn a single JSON event with an array of N sub-items into N events, each
with one sub-item?

Re: how to split the json array into multiple new events

somesoni2 — Tue, 22 Jul 2014 17:09:13 GMT

You can do similar thing for JSON arrays as well using spath.

your base search | spath | rename actions{}.* as * |  |mvexpand id | dedup id | eval temp=mvzip(mvzip(mvzip(type,status,"#"),time_updated,"#"),amount,"#") | mvexpand temp | rex field=temp "(?<type>.*)#(?<status>.*)#(?<time_updated>.*)#(?<amount>.*)" | fields - temp | dedup id amount status time_updated type

Re: how to split the json array into multiple new events

madstop99 — Thu, 25 Aug 2016 20:50:38 GMT

Can someone at least confirm whether this is possible or not? (The question was: how to turn a single JSON event with an array of N sub-items into N events, during parsing, not at search time.)

Re: how to split the json array into multiple new events

naisanza — Mon, 29 Aug 2016 22:30:00 GMT

yes it works as documented. You really have to read the documentation; and it works

Re: how to split the json array into multiple new events

madstop99 — Tue, 30 Aug 2016 05:09:44 GMT

naisanza, were you referring to the use of spath works? Or, do you have hints/comment/method on what I need to do?

Re: how to split the json array into multiple new events

skota524 — Thu, 06 Oct 2016 18:58:46 GMT

It works.. but strange.. we need to copy as it is...
if we give any spaces, its not working...

Re: how to split the json array into multiple new events

TonyLeeVT — Mon, 15 Jan 2018 21:58:21 GMT

I downvoted this post because a link to documentation does not answer the question. including an example would be more helpful.

Re: how to split the json array into multiple new events

MuS — Mon, 15 Jan 2018 22:12:31 GMT

Maybe this example http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath#Basic_examples or this http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath#Extended_examples is more suitable as example?

Re: how to split the json array into multiple new events

tdepuy — Tue, 17 Apr 2018 18:59:30 GMT

The answers here were not helping me. The expanded examples in the spath doc were helpful, but as an exercise I wanted to work through this. SO came up with this that seems to be what you want:

| makeresults
| eval _raw="{
   \"id\":\"123412341234\",
   \"actions\": [ 
     {
       \"type\":\"a\",
       \"status\":\"b\",
       \"amount\": 1,
       \"time_updated\": \"2013-10-14T11:00\"
     },
     {
       \"type\":\"c\",
       \"status\":\"d\",
       \"amount\": 1,
       \"time_updated\": \"2013-10-14T12:00\"
     }
   ]
 }"
 | spath
 | rename "actions{}.type" AS type
 | rename "actions{}.status" AS status
 | rename "actions{}.time_updated" AS time_updated
 | rename "actions{}.amount" AS amount
 | eval zipped=mvzip( time_updated, mvzip( type, mvzip( status, amount ) ))
 | mvexpand zipped
 | eval zipped=split( zipped, "," )
 | eval time_updated=mvindex( zipped, 0 )
 | eval type=mvindex( zipped, 1 )
 | eval status=mvindex( zipped, 2 )
 | eval amount=mvindex( zipped, 3 )
 | table time_updated, id, type, status, amount

Cheers.

Re: how to split the json array into multiple new events

haoban — Thu, 13 Dec 2018 20:45:40 GMT

how about the dynamic field name?
such as :
{"Troj/DocDl-QUA": 4, "CXmail/OleDl-AU": 44, "CXmail/EncDoc-B": 6, "Troj/DocDl-QVV": 10, "Troj/DocDl-QVQ": 10, "Troj/DocDl-QWV": 7, "CXmail/OleDl-AD": 341, "Troj/DocDl-QWN": 25, "Troj/DocDl-QVF": 4, "Troj/DocDl-QVC": 8}

{"Troj/DocDl-QUA": 4, "CXmail/OleDl-AU": 591, "Troj/DocDl-QOQ": 10, "Troj/DocDl-QUE": 9, "Troj/RtfExp-EQ": 1, "Troj/DocDl-QTD": 5, "Troj/DocDl-QUI": 21, "CXmail/OleDl-AD": 868, "CXmail/OleDl-V": 9, "Troj/PDFDown-X": 4}

I want to transfer it like:
Troj/DocDl-QUA 4
CXmail/OleDl-AU 44
CXmail/EncDoc-B 6
Troj/DocDl-QVV 10
Troj/DocDl-QVQ 10
Troj/DocDl-QWV 7
CXmail/OleDl-AD 341
Troj/DocDl-QWN 25
Troj/DocDl-QVF 4
Troj/DocDl-QVC 8
Troj/DocDl-QUA 4
CXmail/OleDl-AU 591
Troj/DocDl-QOQ 10
Troj/DocDl-QUE 9
Troj/RtfExp-EQ 1
Troj/DocDl-QTD 5
Troj/DocDl-QUI 21
CXmail/OleDl-AD 868
CXmail/OleDl-V 9
Troj/PDFDown-X 4

so I can sum the same virus type and draw chart. Thanks!

Re: how to split the json array into multiple new events

piotrjustyna — Mon, 04 Feb 2019 11:41:32 GMT

Awesome, precisely what I needed!

Re: how to split the json array into multiple new events

kamlesh_vaghela — Mon, 04 Feb 2019 15:45:32 GMT

Trying with a different approach to making search easy. Just using the required portion of JSON content in spath, kv and rocking.

| makeresults
 | eval _raw="{
    \"id\":\"123412341234\",
    \"actions\": [ 
      {
        \"type\":\"a\",
        \"status\":\"b\",
        \"amount\": 1,
        \"time_updated\": \"2013-10-14T11:00\"
      },
      {
        \"type\":\"c\",
        \"status\":\"d\",
        \"amount\": 1,
        \"time_updated\": \"2013-10-14T12:00\"
      }
    ]
  }" | spath
  | spath path=actions{} output=actions | mvexpand actions | eval _raw=actions | kv  | table time_updated, id, type, status, amount

UPDATED: Replacing mvexpand with stats command.

| makeresults
  | eval _raw="{
     \"id\":\"123412341234\",
     \"actions\": [ 
       {
         \"type\":\"a\",
         \"status\":\"b\",
         \"amount\": 1,
         \"time_updated\": \"2013-10-14T11:00\"
       },
       {
         \"type\":\"c\",
         \"status\":\"d\",
         \"amount\": 1,
         \"time_updated\": \"2013-10-14T12:00\"
       }
     ]
   }" | spath
   | spath path=actions{} output=actions | stats count by id,actions | eval _raw=actions | kv  | table time_updated, id, type, status, amount

Thanks

Re: how to split the json array into multiple new events

twinspop — Mon, 27 Jan 2020 22:50:35 GMT

best solution on this page. thanks!

Re: how to split the json array into multiple new events

kamlesh_vaghela — Tue, 28 Jan 2020 04:14:34 GMT

Thanks @twinspop 🙂

Re: how to split the json array into multiple new events

jenipherc — Thu, 11 Feb 2021 17:30:14 GMT

Although these two different approaches yield the same results; the underlying mechanism is different that using "stats" could push too much data to the search head(s) and results in an auto finalized search due to search disk quota.

mvexpand is a distributed streaming command (done at indexing layer) whereas stats is transforming commands (done at search head layer).

https://docs.splunk.com/Documentation/Splunk/8.1.2/Search/Typesofcommands