Archive2
Highlighted

How can I count mv fields by type?

Engager

My data looks like:

{
 parent_id: 1
 child_info: [
             {
               id: 123,
               status: "PASS"
             },
             {
               id: 456,
               status: "FAIL"
             }
          ]
}

I am trying to print the result in format:

parent ID  |  Child_Passed_count  |  Child_Failed_count
 1                 1                           1
Tags (1)
0 Karma
Reply
Highlighted

Re: How can I count mv fields by type?

Builder
| makeresults 
| eval jsonTest = "{
  \"parent_id\": 1,
  \"child_info\": [
              {
                \"id\": \"123\",
                \"status\": \"PASS\"
                      },
              {
                \"id\": \"456\",
                \"status\": \"FAIL\"
                      },
                  {
                \"id\": \"457\",
                \"status\": \"FAIL\"
                      }
      ]
 }" 
| spath input=jsonTest path=parent_id output=parent_id 
| spath input=jsonTest output=id_initial path=child_info{}.id 
| spath input=jsonTest output=status_initial path=child_info{}.status 
| eval val = mvzip(id_initial, status_initial) 
| mvexpand val
| eval mvVal = split(val, ",") 
| eval id = mvindex(mvVal, 0) 
| eval status = mvindex(mvVal, 1) 
| stats count(eval(status=="PASS")) as Child_Passed_count count(eval(status=="FAIL")) as Child_Failed_count by parent_id

parentid ChildPassedcount ChildFailed_count
1 1 2

0 Karma
Reply
Highlighted

Re: How can I count mv fields by type?

Engager

Thank you, but in my production server I have about 500,000 events which means 500,000 parent ID's, and each parent has about 200 child records. When I mvexpand for such fields I run out of memory and the search truncates my result. Is there a way of doing it without mvexpand?

0 Karma
Reply
Highlighted

Re: How can I count mv fields by type?

Communicator

Try using this

<your base search>
  | rex "(?<json>\{.+)" | spath input=json | fields - json
  | rename child_info{}.id AS child_id child_info{}.status AS child_status
  | mvexpand child_status
  | stats count(eval(child_status=="PASS")) as Child_Passed_count count(eval(child_status=="FAIL")) as Child_Failed_count by parent_id
0 Karma
Reply
Highlighted

Re: How can I count mv fields by type?

Esteemed Legend

This is another case of FIX YOUR BROKEN JSON. Here is how you can manually fix it but really, get it fixed at the source:

| makeresults 
| eval _raw = "{
   parent_id: 1
   child_info: [
               {
                 id: 123,
                 status: \"PASS\"
                           },
               {
                 id: 456,
                 status: \"FAIL\"
                           },
              {
                 id: 457,
                 status: \"FAIL\"
                           }
       ]
  }" 

| rename COMMENT AS "Everything above generates BROKEN JSON; everything below is your solution"

| rex mode=sed "s/([\r\n\s]+)([^\r\n\s\"]+):/\1\"\2\":/g s/([^{\[\r\n\s:,])([\r\n\s]+\")/\1,\2/g"
| kv
| chart count BY parent_id child_info{}.status

View solution in original post

Highlighted

Re: How can I count mv fields by type?

Engager

Actually I didn't add double quotes while asking this question, the data in production server does have it.
I was trying it with stats, but wasn't giving expected result. Thank You for you help.

0 Karma
Reply
Highlighted

Re: How can I count mv fields by type?

Esteemed Legend

That would have saved me 90% of my effort.

0 Karma
Reply