topic Re: Extracting Fields and Values from JSON Data in Getting Data In

Extracting Fields and Values from JSON Data

ahallak2016 — Wed, 02 Aug 2017 04:47:23 GMT

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?

Re: Extracting Fields and Values from JSON Data

ahallak2016 — Wed, 02 Aug 2017 05:58:11 GMT

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.

Re: Extracting Fields and Values from JSON Data

niketn — Wed, 02 Aug 2017 06:08:35 GMT

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.

Re: Extracting Fields and Values from JSON Data

niketn — Wed, 02 Aug 2017 15:02:11 GMT

@ahallak2016, will you always have 7 values?

Re: Extracting Fields and Values from JSON Data

ahallak2016 — Fri, 04 Aug 2017 05:51:45 GMT

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

Re: Extracting Fields and Values from JSON Data

ahallak2016 — Fri, 04 Aug 2017 05:54:28 GMT

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.

Re: Extracting Fields and Values from JSON Data

niketn — Fri, 04 Aug 2017 06:08:37 GMT

@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