Get dynamic json property names

[tmortiboy]

I have some structured json logs that indicate some validation errors, and depending on the error, a different property is set with the value that caused the error. eg:

{ "validationError": { "property" : { "title" : "" }, "errorType" : "Empty" } }
{ "validationError": { "property" : { "category" : [] }, "errorType" : "Empty" } }
{ "validationError": { "property" : { "category" : [ { "categoryId" : 12345 } ] }, "errorType" : "Invalid" } }
{ "validationError": { "property" : { "thing" : { "thingId" : 9999 } }, "errorType" : "Disallowed" } }

I would like to be able to retrieve the property name and its value: eg
property | value | type
---------+-------+-----------
title | | Empty
category | | Empty
category | 12345 | Invalid
thing | 9999 | Disallowed

[javiergn · Accepted solution]

So assuming your field is called yourField, I came up with the following (probably overcomplicated) query:

| rex max_match=0 "(?m)(?<yourField>[^\n]+)"
| fields - _raw
| mvexpand yourField
| spath input=yourField path=validationError.property output=propertyRaw
| spath input=yourField path=validationError.errorType output=type
| fields - yourField
| spath input=propertyRaw
| rex field=propertyRaw max_match=1 "(?msi)\"(?<property>[^\"]+)\"\s?:"
| fields - propertyRaw
| eval value = ""
| eval temp = 'category{}.categoryId'
| foreach * [ eval temp = if (match("<<FIELD>>", property), toString('<<FIELD>>'), "") | eval value = value . temp]
| fields property, value, type
| foreach * [ eval <<FIELD>> = if(match(<<FIELD>>, "Null"), null(), <<FIELD>>) ]

Example:

| stats count | fields - count
| eval _raw = "
{ \"validationError\": { \"property\" : { \"title\" : \"\" }, \"errorType\" : \"Empty\" } }
{ \"validationError\": { \"property\" : { \"category\" : [] }, \"errorType\" : \"Empty\" } }
{ \"validationError\": { \"property\" : { \"category\" : [ { \"categoryId\" : 12345 } ] }, \"errorType\" : \"Invalid\" } }
{ \"validationError\": { \"property\" : { \"thing\" : { \"thingId\" : 9999 } }, \"errorType\" : \"Disallowed\" } }
"
| rex max_match=0 "(?m)(?<yourField>[^\n]+)"
| fields - _raw
| mvexpand yourField
| spath input=yourField path=validationError.property output=propertyRaw
| spath input=yourField path=validationError.errorType output=type
| fields - yourField
| spath input=propertyRaw
| rex field=propertyRaw max_match=1 "(?msi)\"(?<property>[^\"]+)\"\s?:"
| fields - propertyRaw
| eval value = ""
| eval temp = 'category{}.categoryId'
| foreach * [ eval temp = if (match("<<FIELD>>", property), toString('<<FIELD>>'), "") | eval value = value . temp]
| fields property, value, type
| foreach * [ eval <<FIELD>> = if(match(<<FIELD>>, "Null"), null(), <<FIELD>>) ]

Output, see picture below: