How to find and display unique and missing keys between two JSON object in Splunk?

Question

I have two sets of JSON objects with unique keys in each of them.

First JSON :

{ 
    "Sugar": {
        "prod_rate" : "50",
        "prod_qual" : "Good"
    },
    "Rice": {
        "prod_rate" : "80",
        "prod_qual" : "OK"
    },
   "Potato": {
        "prod_rate" : "87",
        "prod_qual" : "OK"
    }
}

Second JSON :

{ 
    "Sugar": {
        "prod_rate" : "50",
        "prod_qual" : "Good"
    },
    "Wheat": {
        "prod_rate" : "60",
        "prod_qual" : "Good"
    }
}

How can I find the unique keys in any one of JSONs that is not in other and show that in a table.
For example, from the first JSON, the unique key will be "Rice" & "Potato" and the missing key will be "Wheat". I want to make a Splunk table with two columns as unique keys and missing keys to show this data.

Please help, I am new to Splunk.

Thanks

Answer 1

I could do it with below ways considering the datasets in same and different events. Posting each of them below :

--- same event ---

| makeresults 
| eval data1={ 
    \"Sugar\": {
        \"prod_rate\" : \"50\",
        \"prod_qual\" : \"Good\"
    },
    \"Rice\": {
        \"prod_rate\" : \"80\",
        \"prod_qual\" : \"OK\"
    },
   \"Potato\": {
        \"prod_rate\" : \"87\",
        \"prod_qual\" : \"OK\"
    }
}, data2="{ 
    \"Sugar\": {
        \"prod_rate\" : \"50\",
        \"prod_qual\" : \"Good\"
    },
    \"Wheat\": {
        \"prod_rate\" : \"50\",
        \"prod_qual\" : \"Good\"
    
    }
}"
| eval prod_hash_1=json_array_to_mv(json_keys(data1))
| eval prod_hash_2=json_array_to_mv(json_keys(data2))
| eval prod_unique = mvmap(prod_hash_1, if(isnull(mvfind(prod_hash_2, "^".prod_hash_1."$")), prod_hash_1, null()))
| eval prod_missing = mvmap(prod_hash_2, if(isnull(mvfind(prod_hash_1, "^".prod_hash_2."$")), prod_hash_2, null()))
| table data1 data2 prod_unique prod_missing

--- two different events with appendcols ----

index=product_db time="2206589466.725491" | eval data1=if(match(time,"2206589466.725491"),_raw,0)| eval prod_hash_1=json_array_to_mv(json_keys(data1))|table prod_hash_1 |appendcols [ search index=product_db time="2405566003.777518" |eval data2=if(match(time,"2405566003.777518"),_raw,0)
| eval prod_hash_2=json_array_to_mv(json_keys(data2)) ]
| eval prod_unique = mvmap(prod_hash_1, if(isnull(mvfind(prod_hash_2, "^".prod_hash_1."$")), prod_hash_1, null()))
| eval prod_missing = mvmap(prod_hash_2, if(isnull(mvfind(prod_hash_1, "^".prod_hash_2."$")), prod_hash_2, null()))
| table prod_unique prod_missing

--- without appendcols (less longer search, optimized) ---

index=product_db time="2206589466.725491" OR time="2405566003.777518" 
| streamstats c
| eval p_name_{c}=json_array_to_mv(json_keys(_raw)) 
| stats values(p_name_*) as p_name_*
| eval prod_unique = mvmap(prod_hash_1, if(isnull(mvfind(prod_hash_2, "^".prod_hash_1."$")), prod_hash_1, null())) 
| eval prod_missing = mvmap(prod_hash_2, if(isnull(mvfind(prod_hash_1, "^".prod_hash_2."$")), prod_hash_2, null())) 
| table prod_unique prod_missing

--- This below query works, but it's a complete different representatoin than the above ones ---

| makeresults format=json data="[ {\"Sugar\": {
        \"prod_rate\" : \"50\",
        \"prod_qual\" : \"Good\"
    },
    \"Rice\": {
        \"prod_rate\" : \"80\",
        \"prod_qual\" : \"OK\"
    },
   \"Potato\": {
        \"prod_rate\" : \"87\",
        \"prod_qual\" : \"OK\"
    }
}, { 
    \"Sugar\": {
        \"prod_rate\" : \"50\",
        \"prod_qual\" : \"Good\"
    },
    \"Wheat\": {
        \"prod_rate\" : \"50\",
        \"prod_qual\" : \"Good\"
    }
    
    }]"
| fields _raw _time
| eval prod_name=json_array_to_mv(json_keys(_raw))
| streamstats count as row
| eval flag = pow(2, row - 1)
| mvexpand prod_name
| eval {prod_name}=flag
| fields - flag row prod_name
| stats sum(*) as *

Thanks