Join operation doesn't return correct result

[Sophie6]

I have two query tables
table 1
index="k8s_main" namespace="app02013" "EConcessionItemProcessingStartedHandler.createRma PH successfully created RMA" NOT [search index="k8s_main" namespace="app02013" "NonCustomerOrderShippingLabelGeneratedEventsUtil.processShippingLabelEvent Successfully published" | fields LPN]

| rex "LPN\": \"(?<LPN>[^,]+)\"\,"
| rex "location\": \"(?<location>[^,]+)\"\,"
| rex "orderNumber\": \"(?<orderNumber>[^,]+)\"\,"
| rex "orderLineId\": \"(?<orderLineId>[^,]+)\"\,"
| dedup orderLineId
| eval LPN = replace(LPN, "\\[|\\]", "")
| eval location = replace(location, "\\[|\\]", "")
| eval orderNumber = replace(orderNumber, "\\[|\\]", "")
| eval orderLineId = replace(orderLineId, "\\[|\\]", "")
| table LPN location orderNumber orderLineId

table 2
index="k8s_main" namespace="app02013" "Published successfully event=[order-events-avro / com.nordstrom.customer.event.OrderLineReturnReceived]" ECONCESSION

| rex "orderLineId\": \"(?<orderLineId>[^,]+)\"\,"
| rex "orderNumber\": \"(?<orderNumber>[^,]+)\"\,"
| dedup orderLineId
| eval orderNumber = replace(orderNumber, "\"", "")
| eval orderLineId = replace(orderLineId, "\"", "")
| table orderNumber orderLineId

here is my join query:
index="k8s_main" namespace="app02013" "EConcessionItemProcessingStartedHandler.createRma PH successfully created RMA" NOT [search index="k8s_main" namespace="app02013" "NonCustomerOrderShippingLabelGeneratedEventsUtil.processShippingLabelEvent Successfully published" | fields LPN]

| rex "LPN\": \"(?<LPN>[^,]+)\"\,"
| rex "location\": \"(?<location>[^,]+)\"\,"
| rex "orderNumber\": \"(?<orderNumber>[^,]+)\"\,"
| rex "orderLineId\": \"(?<orderLineId>[^,]+)\"\,"
| dedup orderLineId
| eval LPN = replace(LPN, "\\[|\\]", "")
| eval location = replace(location, "\\[|\\]", "")
| eval orderNumber = replace(orderNumber, "\\[|\\]", "")
| eval orderLineId = replace(orderLineId, "\\[|\\]", "")
| table LPN location orderNumber orderLineId
| join left=L right=R where L.orderLineId = R.orderLineId [search index="k8s_main" namespace="app02013" "Published successfully event=[order-events-avro / com.nordstrom.customer.event.OrderLineReturnReceived]" ECONCESSION
| rex "orderLineId\": \"(?<orderLineId>[^,]+)\"\,"
| rex "orderNumber\": \"(?<orderNumber>[^,]+)\"\,"
| dedup orderLineId
| eval orderNumber = replace(orderNumber, "\"", "")
| eval orderLineId = replace(orderLineId, "\"", "")
| table orderNumber orderLineId]

Each table returns unique row.
But the result of the above query returns less data. Please help to find the problem.

[yuanliu]

Default join option is inner. (See Descriptions for the join-options argument.) Why should not you expect less data?

But without looking into join options, let me first point out that the two searches you try to join are performed on the same index during the same period.  This is about the most wasteful use of join in Splunk.  Even without the searches being so, whenever you have an urge to use join in Splunk, you should suppress it for at least three weeks and find an alternative.

Even worse, your regex's suggest that you are trying to use rex to extract from structured JSON.  You should always avoid doing so.  Always work off data structure as your developers intend using robust, QA tested Splunk functionality on them.

The best thing you can do is to follow the usual way of asking an answerable question:

• Illustrate data - raw events (in raw text) returned from index searches such as index="k8s_main" namespace="app02013" "Published successfully event=[order-events-avro / com.nordstrom.customer.event.OrderLineReturnReceived]" ECONCESSION, (anonymize as needed)
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output WITHOUT SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.