Extracting JSON object from JSON array, if value matches

[rishabh10jain]

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!!!

[to4kawa · Accepted solution]

| 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.

[rishabh10jain]

@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!!!

[to4kawa]

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

try mvexpand instead of stats()

[rishabh10jain]

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!!!

[to4kawa]

| 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.