topic Re: Extracting JSON object from JSON array, if value matches in Getting Data In

Extracting JSON object from JSON array, if value matches

rishabh10jain — Fri, 24 Jul 2020 03:39:53 GMT

I've stuck in a scenario, where I want to extract complete JSON object from an JSON array collection on behalf of my search input criteria or on the basis of id match condition. Below is an example :-

{
"message": {
messageHeader: "MessageHeader",
"messageList": [{
"messageName": "messageNameA",
"messageValue": "messageValueA",
"messageId": "A_Value"
"messageStart": "StartDate_Time_Value1",
"messageEnd": "EndDate_Time_Value_1"
"messageConsumerCount": "Count_MessageA"
}, {
"messageName": "messageNameB",
"messageValue": "messageValueB",
"messageId": "B_Value"
"messageStart": "StartDate_Time_Value1",
"messageEnd": "EndDate_Time_Value_1"
"messageConsumerCount": "Count_MessageB"
}, {
"messageName": "messageNameC",
"messageValue": "messageValueC",
"messageId": "C_Value"
"messageStart": "StartDate_Time_Value1",
"messageEnd": "EndDate_Time_Value_1"
"messageConsumerCount": "Count_MessageC"
}
],
"messageTotalConsumerCount": "Total Value of Header 1"
},
"severity": "info"
}, {
"message": {
messageHeader: "MessageHeader",
"messageList": [{
"messageName": "messageNameA",
"messageValue": "messageValueA",
"messageId": "A_Value"
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2"
"messageConsumerCount": "Count_MessageA"
}, {
"messageName": "messageNameC",
"messageValue": "messageValueC",
"messageId": "C_Value"
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2"
"messageConsumerCount": "Count_MessageC"
}, {
"messageName": "messageNameB",
"messageValue": "messageValueB",
"messageId": "B_Value"
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2"
"messageConsumerCount": "Count_MessageB"
}, {
"messageName": "messageNameD",
"messageValue": "messageValueD",
"messageId": "D_Value"
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2"
"messageConsumerCount": "Count_MessageD"
}
],
"messageTotalConsumerCount": "Total Value of Header 1"
},
"severity": "info"
}

In the above JSON, I want to retrieve JSON object on the basis of "messageId" = "B_Value". So my desire result should be :

{
"messageName": "messageNameB",
"messageValue": "messageValueB",
"messageId": "B_Value"
"messageStart": "StartDate_Time_Value1",
"messageEnd": "EndDate_Time_Value_1"
"messageConsumerCount": "Count_MessageB"
},

{
"messageName": "messageNameB",
"messageValue": "messageValueB",
"messageId": "B_Value"
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2"
"messageConsumerCount": "Count_MessageB"
}

The sequence of messageId can be different, as in the JSON "B_Value" occurrence is second and third respectively.

Let me know if I need to clarify more.

Thanks in Advance!!!

Re: Extracting JSON object from JSON array, if value matches

to4kawa — Fri, 24 Jul 2020 08:03:45 GMT

| makeresults | eval _raw="[{\"message\":{\"messageHeader\":\"MessageHeader\",\"messageList\":[{\"messageName\":\"messageNameA\",\"messageValue\":\"messageValueA\",\"messageId\":\"A_Value\",\"messageStart\":\"StartDate_Time_Value1\",\"messageEnd\":\"EndDate_Time_Value_1\",\"messageConsumerCount\":\"Count_MessageA\"},{\"messageName\":\"messageNameB\",\"messageValue\":\"messageValueB\",\"messageId\":\"B_Value\",\"messageStart\":\"StartDate_Time_Value1\",\"messageEnd\":\"EndDate_Time_Value_1\",\"messageConsumerCount\":\"Count_MessageB\"},{\"messageName\":\"messageNameC\",\"messageValue\":\"messageValueC\",\"messageId\":\"C_Value\",\"messageStart\":\"StartDate_Time_Value1\",\"messageEnd\":\"EndDate_Time_Value_1\",\"messageConsumerCount\":\"Count_MessageC\"}],\"messageTotalConsumerCount\":\"Total Value of Header 1\"},\"severity\":\"info\"},{\"message\":{\"messageHeader\":\"MessageHeader\",\"messageList\":[{\"messageName\":\"messageNameA\",\"messageValue\":\"messageValueA\",\"messageId\":\"A_Value\",\"messageStart\":\"StartDate_Time_Value2\",\"messageEnd\":\"EndDate_Time_Value_2\",\"messageConsumerCount\":\"Count_MessageA\"},{\"messageName\":\"messageNameC\",\"messageValue\":\"messageValueC\",\"messageId\":\"C_Value\",\"messageStart\":\"StartDate_Time_Value2\",\"messageEnd\":\"EndDate_Time_Value_2\",\"messageConsumerCount\":\"Count_MessageC\"},{\"messageName\":\"messageNameB\",\"messageValue\":\"messageValueB\",\"messageId\":\"B_Value\",\"messageStart\":\"StartDate_Time_Value2\",\"messageEnd\":\"EndDate_Time_Value_2\",\"messageConsumerCount\":\"Count_MessageB\"},{\"messageName\":\"messageNameD\",\"messageValue\":\"messageValueD\",\"messageId\":\"D_Value\",\"messageStart\":\"StartDate_Time_Value2\",\"messageEnd\":\"EndDate_Time_Value_2\",\"messageConsumerCount\":\"Count_MessageD\"}],\"messageTotalConsumerCount\":\"Total Value of Header 1\"},\"severity\":\"info\"}]" | spath {}.message.messageList{} output=messageList | stats count by messageList | spath input=messageList | fields - count messageList

your JSON is not valid.
If your JSON is valid like above, try spath and search as_you_like.

Re: Extracting JSON object from JSON array, if value matches

rishabh10jain — Sat, 25 Jul 2020 20:26:46 GMT

@to4kawa - Thanks for your reply, your solutions works for me. Actually I've just modified the actual JSON and tried to combine two separate JSON, to better explain my query. You've resolved it.

Now after seeing your solution I got stuck in count part. Actual JSON contains count field also, below is example. Now as per your solution you are removing count field by "field - count messageList", due to this the original count field is also not displaying, that causes issue to me.

Original Valid JSON -

[{
"message": {
"messageHeader": "MessageHeader",
"messageList": [{
"messageName": "messageNameA",
"messageValue": "messageValueA",
"messageId": "A_Value",
"messageStart": "StartDate_Time_Value1",
"messageEnd": "EndDate_Time_Value_1",
"messageConsumerCount": "Count_MessageA",
"count": "MessageCount"
}, {
"messageName": "messageNameB",
"messageValue": "messageValueB",
"messageId": "B_Value",
"messageStart": "StartDate_Time_Value1",
"messageEnd": "EndDate_Time_Value_1",
"messageConsumerCount": "Count_MessageB",
"count": "MessageCount"
}, {
"messageName": "messageNameC",
"messageValue": "messageValueC",
"messageId": "C_Value",
"messageStart": "StartDate_Time_Value1",
"messageEnd": "EndDate_Time_Value_1",
"messageConsumerCount": "Count_MessageC",
"count": "MessageCount"
}],
"messageTotalConsumerCount": "Total Value of Header 1"
},
"severity": "info"
},
{
"message": {
"messageHeader": "MessageHeader",
"messageList": [{
"messageName": "messageNameA",
"messageValue": "messageValueA",
"messageId": "A_Value",
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2",
"messageConsumerCount": "Count_MessageA",
"count": "MessageCount"
}, {
"messageName": "messageNameC",
"messageValue": "messageValueC",
"messageId": "C_Value",
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2",
"messageConsumerCount": "Count_MessageC",
"count": "MessageCount"
}, {
"messageName": "messageNameB",
"messageValue": "messageValueB",
"messageId": "B_Value",
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2",
"messageConsumerCount": "Count_MessageB",
"count": "MessageCount"
}, {
"messageName": "messageNameD",
"messageValue": "messageValueD",
"messageId": "D_Value",
"messageStart": "StartDate_Time_Value2",
"messageEnd": "EndDate_Time_Value_2",
"messageConsumerCount": "Count_MessageD",
"count": "MessageCount"
}],
"messageTotalConsumerCount": "Total Value of Header 1"
},
"severity": "info"
}
]

After applying your query, in the output I'm not able to get count field.

Thanks!!!

Re: Extracting JSON object from JSON array, if value matches

to4kawa — Sat, 25 Jul 2020 22:54:14 GMT

If It doesn't work with that you haven't presented, It's unfair.

try mvexpand instead of stats()

Re: Extracting JSON object from JSON array, if value matches

rishabh10jain — Tue, 28 Jul 2020 05:32:43 GMT

Hi @to4kawa ,

I tried with mvexpand but it's not working for me. Can you please send the complete query that how we can use it. Although I've figure out the another way to resolve my issue, i.e mentioned below, but not able to use mvexpand.

stats values(*) as * by List

and than apply table to get data in tabular format.

Thanks!!!

Re: Extracting JSON object from JSON array, if value matches

to4kawa — Tue, 28 Jul 2020 09:02:28 GMT

| makeresults
| eval tmp=mvrange(0,100)
| stats count by tmp
| fields - count

and

| makeresults
| eval tmp=mvrange(0,100)
| mvexpand tmp

both extracts multivalues. stats by can extract multivalue without limits.conf.
JSON have many multivalues.

It needs to be used according to the situation.