How to prevent values from appearing twice in a single cell of a table

[andres91302]

Hello Everyone I hope you are safe and sound,

I'm extracting values from events that come in a Json format and after that I want to create a Table were I can see each ID and the product thy bought from the store but I am always getting within a single cell the same value repeated two times and when I try to do a stats count then... it is also count twice...

This is my code:

index=purchase_store_x1
| rex mode=sed "s/^(?i)(?:(?!{).)+//g"
| spath
| search BodyJson.name="pdone.ok"
| rename BodyJson.product.ID as PRODUCT
| rename BodyJson.ID.CX.Unique as ID
| table ID PRODUCT
| sort -ID

and so instead of getting the ID asociaed with the product purchased I get something like this:

ID	PRODUCT
31254 31254	XUI45 XUI45
54581 54581	XUI8 XUI45
47851 47851	XUIE58 XUI45

How can I just a normal table without having the same value repeated twice in the cell? THANK YOU SO MUCH for your help,

[bowesmana · Accepted solution]

@andres91302 

It looks like ID is always duplicated, but product can be different, so if that's the case

..your_search..
| eval ID=mvdedup(ID), PRODUCT=mvdedup(PRODUCT)
| table ID PRODUCT
| sort -ID

or

..your_search..
| eval ID=mvdedup(ID)
| stats values(PRODUCT) by ID
| sort -ID

use whichever suits your data better

[andres91302]

Thank you so much! 10/10

[richgalloway]

Try mvexpand.

index=purchase_store_x1
| rex mode=sed "s/^(?i)(?:(?!{).)+//g"
| spath
| search BodyJson.name="pdone.ok"
| rename BodyJson.product.ID as PRODUCT
| rename BodyJson.ID.CX.Unique as ID
| mvexpand PRODUCT
| table ID PRODUCT
| sort -ID

[andres91302]

Thank you so much for your help this was excellent