How to count distinct items in nested fields?

[aj_54321]

Hi! I can't seem to figure out how to get a count of each operation in a document like below:

{ [-]
   request_id: 12345
   revision: 123
   other_field: stuff
   my_precious: { [-]
     1648665400.774453: { [-]
       keys: [ [-]
         key:key1
       ]
       op: operation_1
     }
     1648665400.7817056: { [-]
       keys: [ [-]
         key:key2
       ]
       op: operation_2
     }
     1648665400.7847242: { [-]
       keys: [ [-]
         key:key4
       ]
       op: operation_1
     }
     1648665400.7886434: { [-]
     	keys: [ [-]
         key:key5
       ]
       op: operation_3
     }
     1648665400.7932374: { [-]
     	keys: [ [-]
         key:key3
       ]
       op: operation_2
     }

 I want to be able to see the count of each operation. For example, the above would yield:

operation_1: 2

operation_2: 2

operation_3: 1

I've tried with the following rex, which is unreliable tbh, as there could be other documents with " op: ". But not even the following works...

| rex (?<opr>"(?<= op: )\w+") |stats count by opr |

Any help is appreciated!

[somesoni2]

Give this a try

| rex max_match=0 "op:\s*(?<opr>\w+)" |stats count by opr

OR

| rex max_match=0 "\"op\":\s*\"(?<opr>[^\"]+)" |stats count by opr

[ITWhisperer]

This looks like it might be JSON except that you have shown the formatted version. Is it actually JSON? If so, can you share the raw JSON from the event in a code block (as you did with the formatted JSON)?

[aj_54321]

Is there any way to do this with rex? For example, I've tried 

| rex (?<opr>"(?<=op:) \w+")

which should match any word after "op: ", no? But it's not returning any results for some reason. 

[ITWhisperer]

rex works (by default) on the _raw field. What you have shared appears to be formatted JSON. Please can you confirm that your events have unformatted JSON or formatted JSON?

[aj_54321]

The event is formatted as JSON

[yuanliu · Accepted solution]

What @ITWhisperer suggests is that if event is conformant JSON, you would have already fields like my_precious.1648665400.774453.op, my_precious.1648665400.7817056.op, etc; if not, apply "| spath". (C.f., spath.  It is more reliable to use builtin functions to extract structured data.)

To count by op values, use mvexpand.  Because  your op fields have deep paths, you also need to flatten them with rename, then use foreach to consolidate them into a single field.

| rename my_precious.*.*.op as *_*_op ``` flatten paths ```
| foreach *_op ``` consolidate op ```
    [eval my_op=mvappend(my_op, '<<FIELD>>')]
| mvexpand my_op
| stats count by my_op

With sample data, output is

my_op	count
operation_1	2
operation_2	2
operation_3	1

[aj_54321]

Thanks for this. Is there a way to avoid empty values? Empty values are accounting for most of the results.

[ITWhisperer]

What do you mean by "empty" values? If it is an empty string (as opposed to the field not being there), you could add an if condition to the eval

| foreach *_op ``` consolidate op ```
    [eval my_op=if('<<FIELD>>'=="",my_op,mvappend(my_op, '<<FIELD>>'))]