How to fix query to prevent Duplication of events when searching multi-value fields in Json using mvzip and mvexpand?

[Marian]

Here is a reduced version of my JSON:

{
   records: [
     {
       errors: 4
       name: name1
       plugin: p1
       type: type1
     }
     {
       errors: 7
       name: name2
       plugin: p1
       type: type2
     }
     {
       errors: 0
       name: name3
       plugin: p2
       type: type3
     }
   ]
   session: {
     document: my_doc
     user: me
     version: 7.1
   }
}

There are 3 records in records{} so I expect to get 3 events using mvexpand, but I get 6 events. I'm using a similar query I've found in an answer in this community:

| spath
| rename records{}.name AS name, records{}.type AS type, records{}.plugin as plugin, records{}.errors as errors
| eval x=mvzip(mvzip(mvzip(name,type),plugin),errors)
| mvexpand x
| eval x=split(x,",")
| eval name=mvindex(x,0)
| eval type=mvindex(x,1)
| eval plugin=mvindex(x,2)
| eval errors=mvindex(x,3)
| table name, type, plugin, errors

I get 6 rows instead of 3:

name	type	plugin	errors
name1	type1	p1	4
name2	type2	p1	7
name3	type3	p2	0
name1	type1	p1	4
name2	type2	p1	7
name3	type3	p2	0

Any suggestion how to fix the query to avoid the duplication?  Thanks!

[ITWhisperer]

The search works with example data - here is a runanywhere example

| makeresults
| eval _raw="{
   \"records\": [
     {
       \"errors\": 4,
       \"name\": \"name1\",
       \"plugin\": \"p1\",
       \"type\": \"type1\"
     },
     {
       \"errors\": 7,
       \"name\": \"name2\",
       \"plugin\": \"p1\",
       \"type\": \"type2\"
     },
     {
       \"errors\": 0,
       \"name\": \"name3\",
       \"plugin\": \"p2\",
       \"type\": \"type3\"
     }
   ],
   \"session\": {
     \"document\": \"my_doc\",
     \"user\": \"me\",
     \"version\": 7.1
   }
}"
| spath
| rename records{}.name AS name, records{}.type AS type, records{}.plugin as plugin, records{}.errors as errors
| eval x=mvzip(mvzip(mvzip(name,type),plugin),errors)
| mvexpand x
| eval x=split(x,",")
| eval name=mvindex(x,0)
| eval type=mvindex(x,1)
| eval plugin=mvindex(x,2)
| eval errors=mvindex(x,3)
| table name, type, plugin, errors

[Marian]

This example indeed works. So I don't understand why it doesn't work with a real json.

I ran:

| table _raw

I got this:

{"session": {"version": "7.1", "user": "me", "document": "my_doc"}, "records": [{"plugin": "p1", "type": "type1", "name": "name1", "errors": 4}, {"plugin": "p1", "type": "type2", "name": "name2", "errors": 7}, {"plugin": "p2", "type": "type3", "name": "name3", "errors": 0}]}

No duplications in _raw, and only one event.

What is the difference between the makeresults example and a json with the same data?

[harishalipaka]

@Marian - Use dedup or mvdedup commands 

| eval x=mvdedup(mvzip(mvzip(mvzip(name,type),plugin),errors))

[Marian · Accepted solution]

mvdedup removed the duplication, thanks!