spath - extracting data from groups within JSON

[Mick_OBrien]

Hi All,

Hopefully someone can help with this.   We have logs that contain JSON where one of the fields can have multiple groups/entries - I would like to unwind/expand the groups to have a separate output per line.  I think I have to use mvzip command but I'm having issues with syntax.  Example data/query below...

| makeresults format=json Data="[
{
\"event\": \"AGREEMENT_ACTION_COMPLETED\",
\"participantUserEmail\": \"123456789@test.com\",
\"agreement\": {
\"id\": \"ABCDEFGHIJKLMNOPQRSTUVWXYZ\",
\"status\": \"OUT_FOR_SIGNATURE\",
\"participantSetsInfo\": {
\"participantSets\": [
{
\"memberInfos\": [
{
\"id\": \"abcdefg\",
\"email\": \"abcdefg@test.com\",
\"company\": null,
\"name\": \"test o'test\",
\"privateMessage\": null,
\"status\": \"ACTIVE\"
}
],
\"order\": \"1\",
\"role\": \"SIGNER\",
\"status\": \"WAITING_FOR_OTHERS\",
\"id\": \"abcdefg1234\",
\"name\": null,
\"privateMessage\": null
},
{
\"memberInfos\": [
{
\"id\": \"hijklmno\",
\"email\": \"hijklmno@test.com\",
\"company\": null,
\"name\": null,
\"privateMessage\": null,
\"status\": \"ACTIVE\"
}
],
\"order\": \"1\",
\"role\": \"SIGNER\",
\"status\": \"WAITING_FOR_MY_SIGNATURE\",
\"id\": \"hijklmno1234\",
\"name\": null,
\"privateMessage\": null
}
]
},
\"documentsInfo\": null,
\"agreementViewRequest\": null
}
}]"
| spath output=eventType path=event
| spath output=agreementId path=agreement.id
| spath output=agreementStatus path=agreement.status
| spath output=participantUserEmail path=participantUserEmail
| rename participantSets{}.membersInfos{}.email as memberEmail, participantSets{}.status as memberStatus
| table _time, agreementId, eventType, agreementStatus, participantUserEmail, memberEmail, memberStatus

I still see only one line output and the 'memberEmail' and 'memberStatus' fields are showing as blank where as I want to see one line out to match every entry under 'participantSets' field.

Any help appreciated.

[deepakc · Accepted solution]

Replace with this and see if that gives you the results.

| spath output=eventType path=event
| spath output=agreementId path=agreement.id
| spath output=agreementStatus path=agreement.status
| spath output=participantUserEmail path=participantUserEmail
| spath output=participantSets path=agreement.participantSetsInfo.participantSets{}
| mvexpand participantSets
| spath input=participantSets output=memberInfos path=memberInfos{}
| mvexpand memberInfos
| spath input=memberInfos path=email output=memberEmail
| spath input=memberInfos path=status output=memberStatus
| table _time, agreementId, eventType, agreementStatus, participantUserEmail, memberEmail, memberStatus

[Mick_OBrien]

@deepakc  - This works - thank you!