Search match multiple values in single field in Elasticsearch

Question

I'm using Elasticsearch for search for all document has string REQUEST and partnerId=2960 and customerId= in message field I'm using this query but nothing return

{
  "query": {
    "bool": {
      "must": [
        {
          "match_phrase": {
            "message": "REQUEST"
          }
        },
        {
          "match_phrase": {
            "message": "partnerId=2960"
          }
        },
        {
          "match_phrase": {
            "message": "customerId="
          }
        }
      ]
    }
  }
}

response:

{
  "took" : 213,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

My data:

{
  "_index": "demoapp-log_2021.05.31",
  "_type": "_doc",
  "_id": "epU5wXkBQRhN_CfKNayH",
  "_version": 1,
  "_score": null,
  "_source": {
    "tags": [
      "beats_input_codec_plain_applied",
      "demoapp-log"
    ],
    "message": [
      "2021-05-31 14:00:21,433 INFO  c.m.v.w.r.CollectionService_v_2_0_0 Line 434 - Inquire to business [b57add0e-4819-4130-9051-0628979b468f] CollectionInquiryRequestMsg_V_1_0_6{action=INQUIRE, transactionId=b57add0e-4819-4130-9051-0628979b468f, partnerId=2960, merchantId=VNP, customerId=091**59, billMonth=, partnerRequestId=6762e6fc-544b-4821-95f0-f4b81b547f3f, additionalInfo={\"ACTION\":\"INQUIRE\",\"VERSION\":\"2.0.0\",\"PARTNER_ID\":2960,\"PROVINCE_ID\":\"VNP\",\"CUSTOMER_ID\":\"091**59\",\"BILL_MONTH\":\"\",\"TRANS_REQUEST_ID\":\"6762e6fc-544b-4821-95f0-f4b81b547f3f\",\"CHANNEL_ID\":\"1\",\"TRANS_DATE_TIME\":\"20210531140021\",\"SECURE_CODE\":\"3354327f5af3ec9e0285c81bf5a4f72e3d2dd3afce703fffd9e2fbec1666e93d\"}, owner=, provinceCode=, paymentCode=, channelId=1, clientIp=, createDate=Mon May 31 14:00:21 ICT 2021, lastUpdate=Mon May 31 14:00:21 ICT 2021, collectionPartnerDate=Mon May 31 14:00:21 ICT 2021, merchantDate=null}",
      "Inquire to business [b57add0e-4819-4130-9051-0628979b468f] CollectionInquiryRequestMsg_V_1_0_6{action=INQUIRE, transactionId=b57add0e-4819-4130-9051-0628979b468f, partnerId=2960, merchantId=VNP, customerId=091**59, billMonth=, partnerRequestId=6762e6fc-544b-4821-95f0-f4b81b547f3f, additionalInfo={\"ACTION\":\"INQUIRE\",\"VERSION\":\"2.0.0\",\"PARTNER_ID\":2960,\"PROVINCE_ID\":\"VNP\",\"CUSTOMER_ID\":\"091**59\",\"BILL_MONTH\":\"\",\"TRANS_REQUEST_ID\":\"6762e6fc-544b-4821-95f0-f4b81b547f3f\",\"CHANNEL_ID\":\"1\",\"TRANS_DATE_TIME\":\"20210531140021\",\"SECURE_CODE\":\"3354327f5af3ec9e0285c81bf5a4f72e3d2dd3afce703fffd9e2fbec1666e93d\"}, owner=, provinceCode=, paymentCode=, channelId=1, clientIp=, createDate=Mon May 31 14:00:21 ICT 2021, lastUpdate=Mon May 31 14:00:21 ICT 2021, collectionPartnerDate=Mon May 31 14:00:21 ICT 2021, merchantDate=null}"
    ],
    "type": "beats",
    "agent": {
      "hostname": "web01"
    },
    "host": {},
    "loglevel": "INFO",
    "logger": "c.m.v.w.r.CollectionService_v_2_0_0",
    "line": "434",
    "ecs": {},
    "input": {
      "type": "log"
    },
    "@version": "1",
    "fields": {
      "service": "payapp",
      "app_id": "demoapp-log"
    },
    "log": {
      "file": {
        "path": "/home/collection/live/logs/c_api.log"
      }
    },
    "@timestamp": "2021-05-31T07:00:21.433Z",
    "index_day": "2021.05.31"
  },
  "fields": {
    "@timestamp": [
      "2021-05-31T07:00:21.433Z"
    ]
  },
  "sort": [
    1622444421433
  ]
}

(https://pastebin.com/UZWmXLpq )

My mapping for index

{
  "demoapp-log_2021.05.31" : {
    "mappings" : {
      "properties" : {
        "@timestamp" : {
          "type" : "date"
        },
        "@version" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "agent" : {
          "properties" : {
            "ephemeral_id" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "hostname" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "id" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "name" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "type" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "version" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        },
        "ecs" : {
          "properties" : {
            "version" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        },
        "fields" : {
          "properties" : {
            "app_id" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "max_bytes" : {
              "type" : "long"
            },
            "service" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        },
        "host" : {
          "properties" : {
            "name" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        },
        "index_day" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "input" : {
          "properties" : {
            "type" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        },
        "line" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "log" : {
          "properties" : {
            "file" : {
              "properties" : {
                "path" : {
                  "type" : "text",
                  "fields" : {
                    "keyword" : {
                      "type" : "keyword",
                      "ignore_above" : 256
                    }
                  }
                }
              }
            },
            "flags" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "offset" : {
              "type" : "long"
            }
          }
        },
        "logger" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "loglevel" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "message" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "tags" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "type" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }
}

(https://pastebin.com/8CMm94MY )

i want to get all document has `REQUEST,partnerId=2960,customerId=` in message like grep `grep REQUEST |grep partnerId=2960 | grep customerId=` — Bui Khac Huy, May 31 '21 at 11:18
Your search doesn't work because "REQUEST" doesn't appear in your message as a standalone word but attached to other word like: "partnerRequestId". With basic index setting, Elastic will split words on spaces and try to match word. If you want to match with partial word, you should use something like [N-GRAM tokenizer][1] or a [regex query][2] (but regex is bad for performance) [1]:https://www.elastic.co/guide/en/elasticsearch/reference/current/analysis-ngram-tokenizer.html [2]:https://www.elastic.co/guide/en/elasticsearch/reference/6.8/query-dsl-regexp-query.html — cylon86, May 31 '21 at 11:38

cylon86 · Accepted Answer · 2021-05-31T13:46:15.403

(I updated my answer below based on your comment)

Your search doesn't work because "REQUEST" doesn't appear in your message as a standalone word but attached to other word like: "partnerRequestId" or "_REQUEST_".

So if you want "REQUEST" to match with "_REQUEST_" in a case sensitive manner, you must change the analyzer of the message field. By default the analyzer doesn't split on underscore.

First, you need to create a new index with a custom mapping (you can later reindex your existing index into this new one).

Example of an index with only the "message" field and an analyser that split on underscore and non word character (see the doc for more on tokenizer and analyzer):

PUT my-index
{
  "settings": {
    "analysis": {
      "analyzer": {
        "my_analyzer": {
          "tokenizer": "underscore"
        }
      },
      "tokenizer": {
        "underscore": {
          "type": "pattern",
          "pattern": "[_\\W]+"
        }
      }
    }
  },
  "mappings" : {
    "properties" : {
      "message" : {
        "type" : "text",
         "analyzer":"my_analyzer"
        }
      }
    }
}

Then you can add your document to this new index:

POST my-index/_doc
{
  "message": [
      "2021-05-31 14:00:21,433 INFO  c.m.v.w.r.CollectionService_v_2_0_0 Line 434 - Inquire to business [b57add0e-4819-4130-9051-0628979b468f] CollectionInquiryRequestMsg_V_1_0_6{action=INQUIRE, transactionId=b57add0e-4819-4130-9051-0628979b468f, partnerId=2960, merchantId=VNP, customerId=091**59, billMonth=, partnerRequestId=6762e6fc-544b-4821-95f0-f4b81b547f3f, additionalInfo={\"ACTION\":\"INQUIRE\",\"VERSION\":\"2.0.0\",\"PARTNER_ID\":2960,\"PROVINCE_ID\":\"VNP\",\"CUSTOMER_ID\":\"091**59\",\"BILL_MONTH\":\"\",\"TRANS_REQUEST_ID\":\"6762e6fc-544b-4821-95f0-f4b81b547f3f\",\"CHANNEL_ID\":\"1\",\"TRANS_DATE_TIME\":\"20210531140021\",\"SECURE_CODE\":\"3354327f5af3ec9e0285c81bf5a4f72e3d2dd3afce703fffd9e2fbec1666e93d\"}, owner=, provinceCode=, paymentCode=, channelId=1, clientIp=, createDate=Mon May 31 14:00:21 ICT 2021, lastUpdate=Mon May 31 14:00:21 ICT 2021, collectionPartnerDate=Mon May 31 14:00:21 ICT 2021, merchantDate=null}",
      "Inquire to business [b57add0e-4819-4130-9051-0628979b468f] CollectionInquiryRequestMsg_V_1_0_6{action=INQUIRE, transactionId=b57add0e-4819-4130-9051-0628979b468f, partnerId=2960, merchantId=VNP, customerId=091**59, billMonth=, partnerRequestId=6762e6fc-544b-4821-95f0-f4b81b547f3f, additionalInfo={\"ACTION\":\"INQUIRE\",\"VERSION\":\"2.0.0\",\"PARTNER_ID\":2960,\"PROVINCE_ID\":\"VNP\",\"CUSTOMER_ID\":\"091**59\",\"BILL_MONTH\":\"\",\"TRANS_REQUEST_ID\":\"6762e6fc-544b-4821-95f0-f4b81b547f3f\",\"CHANNEL_ID\":\"1\",\"TRANS_DATE_TIME\":\"20210531140021\",\"SECURE_CODE\":\"3354327f5af3ec9e0285c81bf5a4f72e3d2dd3afce703fffd9e2fbec1666e93d\"}, owner=, provinceCode=, paymentCode=, channelId=1, clientIp=, createDate=Mon May 31 14:00:21 ICT 2021, lastUpdate=Mon May 31 14:00:21 ICT 2021, collectionPartnerDate=Mon May 31 14:00:21 ICT 2021, merchantDate=null}"
    ]
}

And finally run this search that does the job:

GET my-index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "message": "partnerId=2960"
          }
        },
        {
          "match": {
            "message": "customerId"
          }
        },
        {
          "match":{
              "message": "REQUEST"
          }
        }
      ]
    }
  }
}

Also I don't know if it is intentional or not but the "message" field is indexed as both a text and a keyword. Looks to me like you don't need the keyword on this field.

Thank for your supporting I want search match REQUEST with case sensitive. You can see in my data has TRANS_REQUEST_ID, REQUEST in middle of 2 _ character is a word? — Bui Khac Huy, May 31 '21 at 13:05
@BuiKhacHuy by default Elastic doesn't split on underscore, you need a custom analyzer for that (see my updated answer) — cylon86, May 31 '21 at 13:41
thanks you so much after change index setting, mapping as you guided, my query had worked. you saved my life — Bui Khac Huy, May 31 '21 at 14:46

Search match multiple values in single field in Elasticsearch

1 Answers1