topic Re: find and extract a json object from a json array based on other json field in Splunk Search

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

asafd — Mon, 26 Sep 2022 15:23:23 GMT

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

Re: find and extract a json object from a json array based on other json field

yuanliu — Sat, 24 Sep 2022 20:32:30 GMT

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

Re: find and extract a json object from a json array based on other json field

asafd — Sun, 25 Sep 2022 07:39:50 GMT

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

student_id	name
456	def

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

Re: find and extract a json object from a json array based on other json field

kamlesh_vaghela — Sun, 25 Sep 2022 08:04:10 GMT

@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

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

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.

Re: find and extract a json object from a json array based on other json field

ITWhisperer — Sun, 25 Sep 2022 08:55:17 GMT

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

Re: find and extract a json object from a json array based on other json field

asafd — Sun, 25 Sep 2022 10:03:58 GMT

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

Re: find and extract a json object from a json array based on other json field

yuanliu — Sun, 25 Sep 2022 23:50:22 GMT

@asafd wrote:
Thanks @yuanliu . But I want to get a line that looks like this:
student_id name
456 def
(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_id	name
456	abc
456	def
456	hij