Check multiple arrays for value without knowing parent node(s)

[mrsampson]

The structure of JSON in my log events is roughly as follows

{
    "Info": {
        "Apps": {
            "ReportingServices": {
                "ReportTags": [
                    "Tag1"
                ],
                "UserTags": [
                    "Tag2",
                    "Tag3"
                ]
            },
            "MessageQueue": {
                "ReportTags": [
                    "Tag1",
                    "Tag4"
                ],
                "UserTags": [
                    "Tag3",
                    "Tag4",
                    "Tag5"
                ]
            },
            "Frontend": {
                "ClientTags": [
                    "Tag12",
                    "Tag47"
                ]
            }
        }
    }
}

The number of fields in "Apps" is unknown, as are their names. Given this structure I need to check if a given tag ("Tag1", "Tag2", ...) exists in in a given array ("ReportTags", "UserTags", [..]), regardless of parent. If it does, I need the distinct names of parent field names that contain this.
Example 1: The input to the query is "ReportTags" and "Tag1". I'd expect it to output both "ReportingServices" and "MessageQueue" because both of them contain a "ReportTags" array that contains "Tag1".
Example 2: The input to the query is "UserTags" and "Tag5". I'd expect it to output only "MessageQueue" because only this one contains a "UserTags" array that contains this "Tag5".

I have looked at various questions on this forum, tried various combinations of mvexpand and such but I have not been able to write a query that does exactly this. Any hints and/or help would be greatly appreciated.

[ITWhisperer]

Something like this?

| spath
| foreach *.ReportTags*
    [| eval fields=if(isnotnull(mvfind('<<FIELD>>',"Tag1")), if(isnull(fields),"<<MATCHSEG1>>",mvappend(fields,"<<MATCHSEG1>>")), fields)]

[yuanliu · Accepted solution]

Small improvements.

• The wildcard should apply to <anything>Tags{}.
• mvfind uses regex.  If you need string match, there is too much work to convert an arbitrary string into regex.  But Splunk's equality operator applies in multivalue context.

So, using foreach suggested by @ITWhisperer, you can do

| foreach *Tags{}
    [| eval fields=mvappend(fields, if('<<FIELD>>' == "Tag4", "<<FIELD>>", null()))]

Your sample data will give

fields

Info.Apps.MessageQueue.ReportTags{} Info.Apps.MessageQueue.UserTags{}

Since 8.2, Splunk introduced a set of JSON functions.  You can actually use a more formal, semantic approach, although the algorithm is messier because iteration capabilities are limited in SPL. (It is also limited as SPL doesn't support recursion.) Here is an illustration.

| eval key = json_array_to_mv(json_keys(_raw))
| mvexpand key
| eval key1 = json_array_to_mv(json_keys(json_extract(_raw, key)))
| mvexpand key1
| eval key = if(isnull(key1), key, key . "." . key1)
| eval key1 = json_array_to_mv(json_keys(json_extract(_raw, key)))
| mvexpand key1
| eval key = if(isnull(key1), key, key . "." . key1)
| eval key1 = json_array_to_mv(json_keys(json_extract(_raw, key)))
| mvexpand key1
| eval key = if(isnull(key1), key, key . "." . key1)
| eval key1 = json_array_to_mv(json_keys(json_extract(_raw, key)))
| eval key = if(isnull(key1), key, key . "." . key1)
| eval value = json_array_to_mv(json_extract(_raw, key))
| where value == "Tag4"

The above code assumes a path depth of 5 even though your data only has depth of 4.  The result is

key	value
Info.Apps.MessageQueue.ReportTags	Tag1 Tag4
Info.Apps.MessageQueue.UserTags	Tag3 Tag4 Tag5

Here is an emulation you can play with and compare with real data

| makeresults
| eval _raw = "{
    \"Info\": {
        \"Apps\": {
            \"ReportingServices\": {
                \"ReportTags\": [
                    \"Tag1\"
                ],
                \"UserTags\": [
                    \"Tag2\",
                    \"Tag3\"
                ]
            },
            \"MessageQueue\": {
                \"ReportTags\": [
                    \"Tag1\",
                    \"Tag4\"
                ],
                \"UserTags\": [
                    \"Tag3\",
                    \"Tag4\",
                    \"Tag5\"
                ]
            },
            \"Frontend\": {
                \"ClientTags\": [
                    \"Tag12\",
                    \"Tag47\"
                ]
            }
        }
    }
}"
| fields - _time
| spath
``` data emulation above ```