How to map extracted keys to values and separate them?

[piyushpandey]

I have logs which contain parts like:
.. { "profilesCount" : { "120000" : 100 , "120001" : 500 , "110105" : 200 , "totalProfilesCount" : 1057}} ..
here the key is accountId and value is the number of profiles in it.

when I use max_count=0 in rex and extract these values I get:
accountId=[12000000, 12000001, 11001005] and pCount=[100, 500, 200] for this example event.

Since these accountIds are not mapped to their corresponding pCount when I visualize them I get

accountId	pCount
12000000	100 500 200
12000001	100 500 200
11001005	100 500 200

how can I map them correctly and show in a table form?

This was my search query:
search <search_logic> | rex max_match=0 "\"(?<account>\d{8})\" : (?<pCount>\d+)"] | stats values(pCount) by account

Thanks in advance

[bowesmana · Accepted solution]

If your data is JSON, then spath or json_extract can extract that, e.g. something like

| eval p=json_extract(_raw, "profilesCount")
| spath input=p
| fields - _raw _time totalProfilesCount p
| transpose

or you can use foreach to make a field of the account, e.g. 

| rex max_match=0 "\"(?<account>\d{8})\" : (?<pCount>\d+)"
| foreach 0 1 2 3 4 5 [ eval n=mvindex(account,<<FIELD>>), {n}=mvindex(pCount,<<FIELD>>) | fields - n]

or you can use zip/expand/extract

| eval zip=mvzip(account, pCount, "=")
| fields - account pCount
| mvexpand zip
| rex field=zip "(?<account>\d{8})=(?<pCount>\d+)"

Hopefully one of these approaches will get you where you want to get to