How to break out multiple key-value json fields with spath and mvexpand?

[Glasses]

Hi,

I am looking for a bit guidance  breaking out multi-kv pairs in json logs.

For example, I have json email logs where each email event may have multiple>>> multivalve fields, which I need separated / formatted as individual lines...

For instance a single email may have multiple attached files, and each file will have a fileName field, fileHash field, and fileExtn field.

like this in the json...

<hash1>  <fileName1>  <fileExtn1>

<hash2>  <fileName2>  <fileExtn2>

<hash3>  <fileName3>  <fileExtn3>

I want to table the each group on a separate line by subject and sender...

The issue is that I can only get 1 of the fields to break out correctly (like <hash?>) but the other fields <fileName?> and <fileExtn> are lumped together like this...

<hash1>      <fileName1> <fileExtn1>

                         <fileName2> <fileExtn2>

                        <fileName3> <fileExtn3>

This works for 1 field,

....
| spath output=hash path=foo{}.blah
| mvexpand hash
| spath input= hash
| table hash subject sender 

but I don't know how to apply this method to multiple fields and make sure the hash, fileName, fileExtn  all line up in a single formatted line with subject and sender...

Any help greatly appreciated, Thank you!

[bowesmana · Accepted solution]

@Glasses

The normal way to mvexpand on multiple fields is to use multiple mvzip commands, e.g.

| eval composite_hash=mvzip(mvzip(hash, fileName, ";"), fileExtn, ";")
| mvexpand comopsite_hash
| rex field=composite_hash "(?<hash>[^;]*);(?<fileName>[^;]*);(?<fileExtn>.*)"
| fields - composite_hash

You can either use rex at the end to recover the fields or a split+mvindex pattern, I've not tested the performance of each.

You can use this to zip up as many fields as you like. 

[Glasses]

Thank you for the explanation, this gets me going in the right direction...