Splunk Search

How to find and extract a json object from a json array based on other json field?

asafd
Explorer

Hi,

I have rows that are json based. each row has a field that looks like this:

{ 
"students" : [
{"id":"123", "name":"abc"},
{"id":"456", "name":"def"},
{"id":"789", "name":"hij"}
],
"student_id" : "456"
}

each row can have multiple students and always just one student_id.

for each row I want to extract the name of the student who's id is equal to the student_id.

how can I do that?

 

I tried this,:

 |spath path=students{} output=students|mvexpand students | spath input=students|multikv| table id, name, student_id

I do get 3 rows like this in the result:

id name student_id
123 abc 456
456 def 456
789 hij 456

 

but when I try to filter the matching row with:

| where id = student_id

I get 0 rows coming back.

 

TIA

Asaf

Labels (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@asafd 

I think your search should work.

Can you please try this? 

 

 

| makeresults 
| eval _raw="{ \"students\" : [ {\"id\":\"123\", \"name\":\"abc\"},{\"id\":\"456\", \"name\":\"def\"},{\"id\":\"789\", \"name\":\"hij\"}],\"student_id\" : \"456\"}" 
| kv
|rename comment as "upto this is sample data" 
| spath path=students{} output=students 
| mvexpand students 
| spath input=students 
| multikv 
| table id, name, student_id
| where id = student_id

 

 

Screenshot 2022-09-25 at 1.31.13 PM.png

You can try this also.

 

| makeresults 
| eval _raw="{ \"students\" : [ {\"id\":\"123\", \"name\":\"abc\"},{\"id\":\"456\", \"name\":\"def\"},{\"id\":\"789\", \"name\":\"hij\"}],\"student_id\" : \"456\"}" 
| spath 
|rename comment as "upto this is sample data" 
| eval t = mvzip('students{}.id','students{}.name') 
| mvexpand t 
| eval id=mvindex(split(t,","),0), name=mvindex(split(t,","),1) 
| table id name student_id
| where id = student_id

 

Screenshot 2022-09-25 at 1.33.41 PM.png

 

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

 

 

 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| spath path=students{} output=students 
| mvexpand students 
| spath input=students 
| where id = student_id
| table student_id, name
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@asafd 

I think your search should work.

Can you please try this? 

 

 

| makeresults 
| eval _raw="{ \"students\" : [ {\"id\":\"123\", \"name\":\"abc\"},{\"id\":\"456\", \"name\":\"def\"},{\"id\":\"789\", \"name\":\"hij\"}],\"student_id\" : \"456\"}" 
| kv
|rename comment as "upto this is sample data" 
| spath path=students{} output=students 
| mvexpand students 
| spath input=students 
| multikv 
| table id, name, student_id
| where id = student_id

 

 

Screenshot 2022-09-25 at 1.31.13 PM.png

You can try this also.

 

| makeresults 
| eval _raw="{ \"students\" : [ {\"id\":\"123\", \"name\":\"abc\"},{\"id\":\"456\", \"name\":\"def\"},{\"id\":\"789\", \"name\":\"hij\"}],\"student_id\" : \"456\"}" 
| spath 
|rename comment as "upto this is sample data" 
| eval t = mvzip('students{}.id','students{}.name') 
| mvexpand t 
| eval id=mvindex(split(t,","),0), name=mvindex(split(t,","),1) 
| table id name student_id
| where id = student_id

 

Screenshot 2022-09-25 at 1.33.41 PM.png

 

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

 

 

 

asafd
Explorer

Thank you very much. This also worked for me eventually, but you solutions are nicer (I hate regex :-)).

| makeresults 
| eval _raw="{\"message\":{\"res\":{\"body\":{\"embeddedObj\":{\"students\":[{\"id\":\"123\",\"name\":\"abc\"},{\"id\":\"456\",\"name\":\"def\"},{\"id\":\"789\",\"name\":\"hij\"}],\"student_id\":\"456\"}}}}}"
| spath
|rename message.res.body.embeddedObj.students{}.id as ids message.res.body.embeddedObj.students{}.name as names message.res.body.embeddedObj.student_id as student_id
| eval student_zip = mvzip(ids, names)|mvexpand student_zip
| rex field=student_zip "^(?<id>.*),(?<name>.*)"
| where id = student_id| table id, name

 

0 Karma

asafd
Explorer

Thanks @yuanliu . But I want to get a line that looks like this:

student_idname
456def

(because the id of "def" is the student_id "456")

0 Karma

yuanliu
SplunkTrust
SplunkTrust

@asafd wrote:

Thanks @yuanliu . But I want to get a line that looks like this:

student_idname
456def

(because the id of "def" is the student_id "456")


That's exactly why I prefaced my answer with "depends on how you want it."  For this effect, you go

 

| spath input=row
| stats values(students{}.name) by student_id
| mvexpand values(students{}.name)
| rename values(students{}.name) AS name

 

student_idname
456abc
456def
456hij
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Depends on how you want the names displayed or used subsequently.  Here's a simple example

 

| spath input=row
| stats values(students{}.name) by student_id

 

row, of source, means the data field that holds this JSON object.  The output will look like

student_id
values(students{}.name)
456
abc
def
hij
Tags (1)
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...