I have an API input that returns a JSON object containing a nested element with multiple dynamically named columns, where date is the dynamic portion. ex. request.20181201.instructions, request.20181101.instructions, request.20181011.instructions, etc
90 % of these fields will have a literal value of "null" or actually be null. I want to only return the columns other than these.
Basically the psuedocode would be
| table request.* |where .value=* AND .value!="null"
Is there any way to do this?
After a lot of searching and taking bits and pieces from a lot of answers that did not address my issue, I was able to get what I wanted with the following. the rename was required due to the special characters in the field names.
The first foreach actually sets the "null" values to NULL, the second sets the "" values to NULL. this allows the transpose option for include_empty = False to work.
| rename *.*.* AS *_*_* , *.*.*{}.* as *_*_*_*
| foreach request*
[ eval <<FIELD>>=if(<<FIELD>>="null", NULL, <<FIELD>>) ]
| foreach codeDropUploadMap*
[ eval <<FIELD>>=if(<<FIELD>>="", NULL, <<FIELD>>) ]
| transpose include_empty=false
After a lot of searching and taking bits and pieces from a lot of answers that did not address my issue, I was able to get what I wanted with the following. the rename was required due to the special characters in the field names.
The first foreach actually sets the "null" values to NULL, the second sets the "" values to NULL. this allows the transpose option for include_empty = False to work.
| rename *.*.* AS *_*_* , *.*.*{}.* as *_*_*_*
| foreach request*
[ eval <<FIELD>>=if(<<FIELD>>="null", NULL, <<FIELD>>) ]
| foreach codeDropUploadMap*
[ eval <<FIELD>>=if(<<FIELD>>="", NULL, <<FIELD>>) ]
| transpose include_empty=false
The standard way would be ...
| streamstats count as recno
| untable recno fieldname fieldvalue
| where NOT like(fieldname,"something")
| xyseries recno fieldname fieldvalue
| fields - recno