How to form query for nested field (Json array) searching?

[Austin_James]

Hi 

I have a field(event_details) that contains a JSON array.

Record 1:
{"event_details":[{"product_id":"P002","price":19.99,"payment_method":"Paypal"}]}

Record 2:
{"event_details":[{"product_id":"P001","price":9.99,"payment_method":"Credit Card"},{"product_id":"P002","price":10,"payment_method":"Credit Card"}]}

Query:
source="sample_Logs.csv" host="si-i-01ab4b9a34d1f49ec.prd-p-gfp5t.splunkcloud.com" sourcetype="csv" | tojson auto(*) | spath "event_details{}.product_id" | search "event_details{}.product_id"=P002

When using the above query I got both records in the response.

But I need only those records with product_id = "P002" only and not with any other product_id in the JSON array. In this case record 1 contains only product_id as P002. I need only that record in the response.

How to form the query for it?

I really appreciate any help you can provide.

Update: Have explained my query properly in the below comment.
 https://community.splunk.com/t5/Splunk-Search/Nested-field-Json-array-searching/m-p/629097/highlight... 

[PickleRick]

Splunk does not handle structure of json events. So you can't just select a particular part of a json structure by some of its fields. Or at least not directly.

You must first do spath to extract just the values of event_details to a multivalued field. Then you have to do mvexpand to split those values to separate events. Those events will contain separate json entities which you can treat with spath again.

[bowesmana]

Add

| where mvcount('event_details{}.product_id')=1

[Austin_James]

Hi @bowesmana 
Thanks for the response.
When adding the mvcount we are adding the criteria for the JSON array to be of length one. Which does not solve my query.
I will explain my case better with the following example:
Consider the following records:

event	event_details
E1	[{"product_id":"P001","price":9.99,"payment_method":"Credit Card"},{"product_id":"P002","price":10,"payment_method":"Credit Card"}]
E2	[{"product_id":"P001","price":9.99,"payment_method":"Credit Card"},{"product_id":"P002","price":10,"payment_method":"Paypal"}]
E3	[{"product_id":"P002","price":19.99,"payment_method":"Paypal"}]
E4	[{"product_id":"P001","price":9.99,"payment_method":"Paypal"},{"product_id":"P002","price":10,"payment_method":"Paypal"}]

source="sample_Logs.csv" host="si-i-01ab4b9a34d1f49ec.prd-p-gfp5t.splunkcloud.com" sourcetype="csv" | tojson auto(*) | spath "event_details{}.payment_method" | where 'event_details{}.payment_method'="Paypal"

When using the above query I got 3 (E2, E3, E4) events in the response as shown in the below image.

I need to filter the events where all the purchases are done only by Paypal. So I am expecting the events E3 and E4 alone. Neglecting the event E2 since one of the purchases has been made by Credit Card.

[bowesmana]

OK, so it's not where the number of elements is 1, but where ALL the values are the same. So, in that case do this

| eval method=mvdedup('event_details{}.payment_method')
| where method="Paypal" AND mvcount(method)=1

which will just remove all duplicates and them require the method match  (=Paypal) and a single variant (mvcount)

[bowesmana · Accepted solution]

Here's an example showing 3 and 4 returned

| makeresults 
| fields - _time
| eval _raw="event	event_details
E1	[{\"product_id\":\"P001\",\"price\":9.99,\"payment_method\":\"Credit Card\"},{\"product_id\":\"P002\",\"price\":10,\"payment_method\":\"Credit Card\"}]
E2	[{\"product_id\":\"P001\",\"price\":9.99,\"payment_method\":\"Credit Card\"},{\"product_id\":\"P002\",\"price\":10,\"payment_method\":\"Paypal\"}]
E3	[{\"product_id\":\"P002\",\"price\":19.99,\"payment_method\":\"Paypal\"}]
E4	[{\"product_id\":\"P001\",\"price\":9.99,\"payment_method\":\"Paypal\"},{\"product_id\":\"P002\",\"price\":10,\"payment_method\":\"Paypal\"}]
"
| multikv forceheader=1 
| table event event_details

``` Above sets up your example dataset ```

| tojson auto(*)
| spath event_details{}.payment_method
| eval method=mvdedup('event_details{}.payment_method')
| where method="Paypal" AND mvcount(method)=1

[bowesmana]

Or you can combine the search and the where to

| where 'event_details{}.product_id'="P002" AND mvcount('event_details{}.product_id')=2