Extracting data from complicated JSON, match a value

[CanadianTrevorS]

I have JSON in the following format:

[
    {
      "nameValues": [], 
      "offeringId": "a"
    }, 
    {
      "nameValues": [
        {
          "name": "key1", 
          "value": "true"
        }, 
        {
          "name": "key2", 
          "value": "value2"
        }
      ], 
      "offeringId": "b"
    }
]

I am looking to match a create a field with the value of one of the one the name value pairs, that matches the offeringId=b. The output being key1=true. All the keyvalue pairs in nameValues would obviously also suffice.

I've struggled with spath, but not sure that you can select a specific offeringId=b like you can do in xpath. Or would I be better off attempting this via regex?

Any help would be much appreciated.

[DalJeanis · Accepted solution]

Okay, I lied, it can be done. It just has to be done in steps.

Here's the whole code, assuming you were looking for the value of "key2" for offeringId="b". A full explanation of what I've done here follows that.

| makeresults | eval source="[{\"nameValues\":[],\"offeringId\":\"a\"},{\"nameValues\":[{\"name\":\"key1\",\"value\":\"true\"},{\"name\":\"key2\",\"value\":\"value2\"}],\"offeringId\": \"b\"}]"
 | spath input=source path="{}"
 | rename "{}" as mystuff
 | mvexpand mystuff 
 | spath input=mystuff
 | table source mystuff offeringId nameValues{}.name nameValues{}.value
 | where offeringId="b"
 | rename nameValues{}.name as MyName, nameValues{}.value as MyValue
 | eval MyNameValue=mvzip(MyName, MyValue,"=")
 | table offeringId MyNameValue
 | mvexpand MyNameValue
 | where match(MyNameValue,"key2")

---

This just creates the test data.

 | makeresults | eval source="[{\"nameValues\":[],\"offeringId\":\"a\"},{\"nameValues\":[{\"name\":\"key1\",\"value\":\"true\"},{\"name\":\"key2\",\"value\":\"value2\"}],\"offeringId\": \"b\"}]"

This spath with a path set to {} will extract the data at that first, highest level.

 | spath input=source path="{}"
 | rename "{}" as mystuff

Now we use mvexpand to break up the JSON into individual transactions at that same, highest level, and then run spath again on the result. At this level, the key/value pairs are connected to the offeringId. We throw away everything that wasn't offeringId = "b".

| mvexpand mystuff 
| spath input=mystuff
| table source mystuff offeringId nameValues{}.name nameValues{}.value
| where offeringId="b"

Now, we change the name from the hideous JSON format to a plan name, and mvzip together the related name/value fields. I'm formatting them here as a single field where the data looks like "key2=value2".

| rename nameValues{}.name as MyName, nameValues{}.value as MyValue
| eval MyNameValue=mvzip(MyName, MyValue,"=")
| table offeringId MyNameValue

Finally, we expand again, and throw away all the rows that don't contain the key value we were looking for.

| mvexpand MyNameValue
| where match(MyNameValue,"key2")

[CanadianTrevorS]

Wow, this was a lot of steps, I am very grateful, I made a slight tweak:

after the

rename nameValues{}.name as MyName, nameValues{}.value as MyValue

I added

| search MyName=keyNameIwasLookingFor AND MyValue=valueIWasLookingFor | table MyName, MyValue

and was able to limit to only include entries that contained keyNameIwasLookingFor=valueIWasLookingFor

[DalJeanis]

Okay, I don't think that's right. If you already knew what the value was, then what was the point of checking the json?

[DalJeanis]

THIS ANSWER, THOUGH SENSIBLE, WAS WRONG. SEE THE OTHER ONE.

Unfortunately, since the offering containers are unnamed, there is no way to differentiate which offering a particular key came from

| makeresults | eval source="[{\"nameValues\":[],\"offeringId\":\"a\"},{\"nameValues\":[{\"name\":\"key1\",\"value\":\"true\"},{\"name\":\"key2\",\"value\":\"value2\"}],\"offeringId\": \"b\"}]"
| spath input=source 
| table source  {}.nameValues{}.name {}.nameValues{}.value {}.offeringId

outputs something like this

source     {}.nameValues{}.name {}.nameValues{}.value   {}.offeringId

(the JSON)  key1                   true                    a

            key2                   value2                  b   

Since there is no cardinality that relates the OfferingId with the keys, there's nothing there to help us pull them out, as far as I can see, in the current JSON structure.

It might be seen more clearly in this example, where there are five keys for the two OfferingIds

| makeresults | eval source=
"[{\"nameValues\":[
       {\"name\":\"key3\",\"value\":\"maybe\"},
       {\"name\":\"key7\",\"value\":\"idunno\"},
       {\"name\":\"key5\",\"value\":\"letmecheckmynotes\"}
       ],
   \"offeringId\":\"a\"},
  {\"nameValues\":[
       {\"name\":\"key1\",\"value\":\"true\"},
       {\"name\":\"key2\",\"value\":\"value2\"}
       ],
   \"offeringId\": \"b\"}]"
 | spath input=source 
 | table source  {}.nameValues{}.name {}.nameValues{}.value {}.offeringId