Filter JSON search results based on another log message in the same search?

[ghostrider]

I have the below search results that will consist of 2 different types of log formats or strings. Log 1:  "MESSAGE "(?<JSON>\{.*\})" and Log 2 : "Published Event for txn_id (?<tx_id>\w+)". Both of these formats or logs or messages will be present in the result of the below search_results.
I want to filter only those logs with Log 1 format that has the same transactionid as the one in the other Log 2 format. I am trying to run the below query. However its giving zero results even though there are common transactionids between these 2 log formats. Is there any way to achieve this? 

{search_results}
| rex field=MESSAGE "(?<JSON>\{.*\})"
| rex field=MESSAGE "Published Event for txn_id (?<tx_id>\w+)"
| spath input=JSON
| where transaction_id == tx_id

[yuanliu]

I think this is the same as https://community.splunk.com/t5/Splunk-Search/How-to-filter-results-based-on-property-inside-JSON-st... in which you didn't specify that the two rex in your original search would be from different events.

To equate two different events, name the transaction ID with the same name, then stats by that common field as @ITWhisperer suggested, then filter out events that do not match.  Since you are only interested in Log 1, i.e., the source with that JSON section, filter out the other one.

{search_results}
| rex field=MESSAGE "(?<JSON>\{.*\})"
| rex field=MESSAGE "Published Event for txn_id (?<transaction_id>\w+)"
| spath input=JSON
| stats values(MESSAGE) as MESSAGE by transaction_id
| where mvcount(MESSAGE) > 1
| eval MESSAGE = mvfilter(!match(MESSAGE, "Published Event for txn_id"))

[ITWhisperer]

Splunk processes a pipeline of events - the where command (for example) is only operating on one event at a time. In order to find the events that share the same transaction_id you need to "gather" the events using the stats command, something like this

{search_results}
| rex field=MESSAGE "(?<JSON>\{.*\})"
| rex field=MESSAGE "Published Event for txn_id (?<tx_id>\w+)"
| spath input=JSON
| eval transaction_id=tx_id
| stats values(MESSAGE) as MESSAGE by transaction_id