Extracting Fields and Values from JSON Data

[ahallak2016]

I have the following Dataset:

{
    "createFormInstanceRequest": {
        "formId": "xxxxxxxxxxxxxxxxxxxxxxx",
        "requestOptions": {
            "requestAction": "SUBMIT"
        },
        "responseOptions": {
            "returnFormDefinition": false,
            "returnFormInfo": false,
            "returnFormData": true
        },
        "formData": {
            "groups": [{
                "groupId": "studentDetails",
                "iterations": [
                    [{
                        "fieldId": "studentName",
                        "value": ["timothy jones"]
                    }, {
                        "fieldId": "studentid",
                        "value": ["sxxxxxx"]
                    }, {
                        "fieldId": "address",
                        "value": ["12/12 some street"]
                    }, {
                        "fieldId": "attendingevent",
                        "value": ["No"]
                    }]
                ]
            }, {
                "groupId": "grades",
                "iterations": [
                    [{
                        "fieldId": "math",
                        "value": ["C"]
                    }, {
                        "fieldId": "PE",
                        "value": ["D"]
                    }, {
                        "fieldId": "english",
                        "value": ["A"]
                    }, {
                        "fieldId": "science",
                        "value": ["B"]
                    }]
                ]
            }]
        }
    }
}

I am struggling to parse the field names and values as everything is placed in either: createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId and createFormInstanceRequest.formData.groups{}.iterations{}{}.value{}.

They both become multivalue fields.

I couldn't get SPATH to work with this data and changing the log format is not an option either.

How can I create fields based on the fieldIDS and assign them the corresponding values in search time?

[niketn]

Following is a run anywhere search to get single value mapping between fieldId and value instead of multivalue. I have used spath. However, you can use pipe commands from mvzip() onward.

| makeresults
| eval jsonData="
 {
     \"createFormInstanceRequest\": {
         \"formId\": \"xxxxxxxxxxxxxxxxxxxxxxx\",
         \"requestOptions\": {
             \"requestAction\": \"SUBMIT\"
         },
         \"responseOptions\": {
             \"returnFormDefinition\": false,
             \"returnFormInfo\": false,
             \"returnFormData\": true
         },
         \"formData\": {
             \"groups\": [{
                 \"groupId\": \"studentDetails\",
                 \"iterations\": [
                     [{
                         \"fieldId\": \"studentName\",
                         \"value\": [\"timothy jones\"]
                     }, {
                         \"fieldId\": \"studentid\",
                         \"value\": [\"sxxxxxx\"]
                     }, {
                         \"fieldId\": \"address\",
                         \"value\": [\"12/12 some street\"]
                     }, {
                         \"fieldId\": \"attendingevent\",
                         \"value\": [\"No\"]
                     }]
                 ]
             }, {
                 \"groupId\": \"grades\",
                 \"iterations\": [
                     [{
                         \"fieldId\": \"math\",
                         \"value\": [\"C\"]
                     }, {
                         \"fieldId\": \"PE\",
                         \"value\": [\"D\"]
                     }, {
                         \"fieldId\": \"english\",
                         \"value\": [\"A\"]
                     }, {
                         \"fieldId\": \"science\",
                         \"value\": [\"B\"]
                     }]
                 ]
             }]
         }
     }
 }
"
| spath input=jsonData path=createFormInstanceRequest.formId output=formId
| spath input=jsonData path=createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId output=fieldId
| spath input=jsonData path=createFormInstanceRequest.formData.groups{}.iterations{}{}.value{} output=value
| eval fieldValue=mvzip(fieldId,value)
| mvexpand fieldValue
| eval fieldValue=split(fieldValue,",")
| eval fieldId=mvindex(fieldValue,0)
| eval value=mvindex(fieldValue,1)
| table formId fieldId value

PS: I have retained formId if you want to perform further correlation with SPL.

[ahallak2016]

Your solution worked once I removed the input parameter as I am searching in my index. It does create the mappings correctly but it does not create fields from the fieldIds.

I guess I was trying to replicate what the sourcetype should have done.

[niketn]

@ahallak2016, yes first part of my query until spath commands were to create mock data. You do not require them.

I had messed up couple of field names as per your question. Possibly because of the same your query is not working as expected. Can you try with the following search?

 index=myIndex sourcetype=json
| rename "createFormInstanceRequest.formData.groups{}.iterations{}{}.value{}" as values,"createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId" as fields, "createFormInstanceRequest.formId" as formId 
| eval fieldValue=mvzip(fields,values)
| mvexpand fieldValue
| eval fieldValue=split(fieldValue,",")
| eval fields=mvindex(fieldValue,0)
| eval values=mvindex(fieldValue,1)
| table formId fields values

[ahallak2016]

Ok, I have come up with a work around, its functional.. but it looks horrible:

index=myIndex sourcetype=json | rename "createFormInstanceRequest.formData.groups{}.iterations{}{}.value{}" as values,"createFormInstanceRequest.formData.groups{}.iterations{}{}.fieldId" as fields 
    | eval var0 = mvindex(fields,0) 
    | eval var1 = mvindex(fields,1) 
    | eval var2 = mvindex(fields,2) 
    | eval var3 = mvindex(fields,3) 
    | eval var4 = mvindex(fields,4) 
    | eval var5 = mvindex(fields,5) 
    | eval var6 = mvindex(fields,6) 
    | eval var7 = mvindex(fields,7)

    | eval {var0} = mvindex(values,0)
    | eval {var1} = mvindex(values,1)
    | eval {var2} = mvindex(values,2)
    | eval {var3} = mvindex(values,3)
    | eval {var4} = mvindex(values,4)
    | eval {var5} = mvindex(values,5)
    | eval {var6} = mvindex(values,6)
    | eval {var7} = mvindex(values,7)

With this, the values of fieldID will be the field names of the actual values relating to student.

[niketn]

@ahallak2016, will you always have 7 values?

[ahallak2016]

At this point yes. I don't know if this will change in the future.