How do I remove dynamically named columns from a table when their values contain a specific string?

[Cuyose]

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?

[DalJeanis]

The standard way would be ...

| streamstats count as recno
| untable recno fieldname fieldvalue
| where NOT like(fieldname,"something")
| xyseries recno fieldname fieldvalue
| fields - recno

[Cuyose · Accepted solution]

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