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
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.
| spath path=students{} output=students
| mvexpand students
| spath input=students
| where id = student_id
| table student_id, name
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.
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
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")
@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 |
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 |