topic Re: Nested field (Json array) searching in Splunk Search

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

Austin_James — Wed, 01 Feb 2023 16:44:24 GMT

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/true#M218519

Re: Nested field (Json array) searching

bowesmana — Wed, 01 Feb 2023 07:21:08 GMT

Add

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

Re: Nested field (Json array) searching

bowesmana — Wed, 01 Feb 2023 07:22:39 GMT

Or you can combine the search and the where to

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

Re: Nested field (Json array) searching

Austin_James — Wed, 01 Feb 2023 08:04:49 GMT

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.

Re: Nested field (Json array) searching

PickleRick — Wed, 01 Feb 2023 09:58:47 GMT

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.

Re: Nested field (Json array) searching

bowesmana — Wed, 01 Feb 2023 10:54:08 GMT

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)

Re: Nested field (Json array) searching

bowesmana — Wed, 01 Feb 2023 11:00:50 GMT

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