topic Re: Parsing JSON with spath command is not returning expected results. in Getting Data In

Parsing JSON with spath command is not returning expected results.

jbutorac3 — Mon, 10 Sep 2018 20:01:48 GMT

I have tried to get after.merchantId a million different ways, but it always comes back blank. I believe I'm missing the obvious:

Search String:
sourcetype="json" auditId=RECIPIENT_ADDED | spath | table _time, after.merchantId
...only _time has values, nothing else

sourcetype="json" auditId=RECIPIENT_ADDED | spath | rename after.merchantId as merchantId, after.leadDays as leadDays | eval x=mvzip(merchantId,leadDays) | table _time,merchantId,leadDays,x
...only _time has values, nothing else


Sample JSON:
{   
     @timestamp:     2018-09-09T19:05:50.077Z   
     @version:   1  
     actingProfileType:  ALL    
     after:  {"phoneNumber":"8005551212","recipientNumber":"************1111","merchantId":"111111112","paperPaymentEnabled":"true","leadDays":"5","Nickname":"Bob Evans","addressOnFile":"false","recipientName":"Bobby Evans","transferMethod":"PAYMENT","merchantZipRequired":"false","providerStatus":"ACTIVE","merchantName":"Bobby Evans"}    
     application:    BACKOFFICE 
     auditId:    RECIPIENT_ADDED    
     browserName:    Chrome 
     browserVersion:     68.0.3440.106  
     clientIp:   192.0.0.1  
     companyId:  11113  
     component:  PAYMENTS   
     instanceId:     1abc2345-67de      
     userId:     11111114   
     userSourceId:   2fgh3456-89ij      
}

Re: Parsing JSON with spath command is not returning expected results.

somesoni2 — Mon, 10 Sep 2018 21:41:16 GMT

The spath command works when your json data is in pure json format. You can use https://jsonlint.com/ to confirm if you raw data is a valid json.

Re: Parsing JSON with spath command is not returning expected results.

DalJeanis — Mon, 12 Nov 2018 04:59:07 GMT

1) Your JSON is missing required commas between key-value pairs.

2) The colons in the time field are confusing the parsing algorithm. In addition, it seems to be breaking each value and inserting space before periods, between pure alpha, pure decimal, and hyphens, and so on.

3) Parsing worked perfectly when we added the required commas and surrounded all keys and values with quotes, but beyond that, we couldn't tell you what other issues there might have been.