DQL parsing on a log line that has free text and nested JSON

[shrilekha-s]

Hi team, I'm trying to extract certain fields(acctAccountNumber,payerAddr and value)from a log line that has a mix of free text followed by a nested JSON - tried different variants but I'm still getting it wrong, any insights please on how to parse this correctly?

fetch logs
| filter matchesPhrase(content, "sendmoneysave")
| parse content, "JSON:contentjson"
| parse contentjson[data], "JSON:data",data[payer], "JSON:payer", payer[acctType], "JSON:acctType", data[amountVO], "JSON:amountVO"
//| parse contentjson[data][payer], "JSON:payer", contentjson[data][acctType],"JSON:acctType", contentjson[data][amountVO],"JSON:amountVO"
| fields timestamp, content, contentjson[data][payer][acctType][acctAccountNumber], contentjson[data][payer][payerAddr],contentjson[data][amountVO][value]

2023-07-05T10:51:42.203+05:30 trace_id=bc94d547c317e981f8a357c7e95ca296  INFO 1 --- [erContainer-C-1] c.f.u.c.util.ReceiveMessageService       : dt.trace_sampled: true, dt.trace_id: b034e72f14a388f463707da83fdb2689, dt.entity.host_group: HOST_GROUP-C6DE8A2EA15DFABA, dt.entity.process_group_instance: PROCESS_GROUP_INSTANCE-500094DBC5C058BC, dt.span_id: 4784dfcfa6ba0185, dt.entity.host: HOST-33FE70BF62D9375A message received: | topicVsMsgtotxn:
{
	"id":"bc94d547c317e981f8a357c7e95ca296",
	"type":"sendMoneySave",
	"data":"
		{
			\"payer\":
				{
					\"acctType\":
						{
							\"acctAccountIfsc\":\"123ABC\",
							\"acctAccountNumber\":\"123456\",
							\"acctAccountType\":\"SAVINGS\",
							\"acctAccountAddrType\":\"ACCOUNT\"
					},
					\"credVO\":null,
					\"amountVO\":null,
					\"respRefVO\":null,
					\"payerAddr\":\"917907162853@okidf\",
					\"payerCode\":\"0000\",
					\"upiAccountId\":null,
					\"consumerId\":null,
					\"consentValue\":null
				},
			\"amountVO\":
				{
					\"currency\":\"INR\",
					\"value\":200.0
				},
			\"signature\":null,
		}
	","data_base64":null
}

[sinisa_zubic · Accepted solution]

Hi @shrilekha-s 

The reason why your query is not working is because the inner json is not a valid json and parsing for it doesn't work. Please have a look at following screenshot

for this particular case, where at the end you have a colon, line break, two tabs and a closed curly bracket I made the query work

fetch logs
| filter matchesPhrase(content, "sendmoneysave")
| parse content, "DATA JSON:contentjson"
| parse contentjson[data], """DATA:data ',\n		}' """
| fieldsAdd data = concat(data,"}")
| parse data, """data? json:datajson""" 
| fields acctAccountNumber=datajson[payer][acctType][acctAccountNumber]
  , payerAddress = datajson[payer][payerAddr]
  , value = datajson[amountVO][value]

and here the result, based on the sample data

Best,
Sini

[shrilekha-s]

Thanks @sinisa_zubic !