elasticsearchelasticsearch-6

terms query not working in elastic search?


Hi I am trying to issue a curl command to retrieve some documents from my elastic search index (mep-reports) how it is not working.

imagine if i do the following curl command it returns the data

curl -XGET 'localhost:52671/mep-reports*/_search?size=1' -H 'Content-Type: application/json' -d @query1.txt

Response

{
  "took": 6,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 100,
    "max_score": null,
    "hits": [
      {
        "_index": "mep-reports",
        "_type": "doc",
        "_id": "99",
        "_score": null,
        "_source": {
          "inventory": "SMS",
          "msg_text": "This is random text",
          "status": "ENROUTE",
          "@timestamp": "2019-09-10T07:06:26.287Z",
          "o_error": "",
          "flight_id": "92348fa1-ca6c-456a-b3b2-85fba2d2deed",
          "recipient": "420736408281",
          "account_id": "a56f7e14-20f9-40e6-90c6-10604140ac5f",
          "sender": "8800111",
          "campaign_id": "6f2abca3-b46d-43f3-91be-3278a8dd7dc0",
          "nof_segments": 1,
          "@version": 1,
          "submission_ts": 1568105380000000,
          "delivery_ts": 1553616888000000,
          "campaign_name": "Starbucks Promotion",
          "flight_name": "Extremely very very long flight",
          "campaign_type": "NON_MARKETING"
        },
        "sort": [
          1568099186287
        ]
      }
    ]
  }
}

query1.txt file content.

{
  "from": 0,
  "size": 10,
  "timeout": "300s",
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "from": "2019-08-31T23:00:00.000Z",
              "to": null,
              "include_lower": true,
              "include_upper": true,
              "format": "yyyy-MM-dd'T'HH:mm:ss.SSSZ",
              "boost": 1.0
            }
          }
        },
        {
          "range": {
            "@timestamp": {
              "from": null,
              "to": "2019-09-12T07:06:26.287Z",
              "include_lower": true,
              "include_upper": true,
              "format": "yyyy-MM-dd'T'HH:mm:ss.SSSZ",
              "boost": 1.0
            }
          }
        },
        {
          "match": {
            "account_id": {
              "query": "a56f7e14-20f9-40e6-90c6-10604140ac5f",
              "operator": "OR",
              "prefix_length": 0,
              "max_expansions": 50,
              "fuzzy_transpositions": true,
              "lenient": false,
              "zero_terms_query": "NONE",
              "auto_generate_synonyms_phrase_query": true,
              "boost": 1.0
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "sort": [
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ]
}

However when i modify my query1.txt to search documents to include terms query on campaign_type it don't work.

   curl -XGET 'localhost:52671/mep-reports*/_search?size=1' -H 'Content-Type: application/json' -d @query.txt

Response

{"took":6,"timed_out":false,"_shards":{"total":5,"successful":5,"skipped":0,"failed":0},"hits":{"total":0,"max_score":null,"hits":[]}}

modified query. query.txt file content

{
  "from": 0,
  "size": 10,
  "timeout": "300s",
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "from": "2019-08-31T23:00:00.000Z",
              "to": null,
              "include_lower": true,
              "include_upper": true,
              "format": "yyyy-MM-dd'T'HH:mm:ss.SSSZ",
              "boost": 1.0
            }
          }
        },
        {
          "range": {
            "@timestamp": {
              "from": null,
              "to": "2019-09-12T07:06:26.287Z",
              "include_lower": true,
              "include_upper": true,
              "format": "yyyy-MM-dd'T'HH:mm:ss.SSSZ",
              "boost": 1.0
            }
          }
        },
        {
          "match": {
            "account_id": {
              "query": "a56f7e14-20f9-40e6-90c6-10604140ac5f",
              "operator": "OR",
              "prefix_length": 0,
              "max_expansions": 50,
              "fuzzy_transpositions": true,
              "lenient": false,
              "zero_terms_query": "NONE",
              "auto_generate_synonyms_phrase_query": true,
              "boost": 1.0
            }
          }
        },
        {
          "terms": {
            "campaign_type": [
              "NON_MARKETING"
            ],
            "boost": 1.0
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "sort": [
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ]
}

I would expect the above query to return some results . the only difference is i have added terms query on campaign type field .

this is my elastic search mapping document

{
  "mappings": {
    "doc": {
      "properties": {
        "@timestamp": {
          "type": "date"
        },
        "@version": {
          "type": "text"
        },
        "account_id": {
          "type": "keyword"
        },
        "campaign_id": {
          "type": "keyword"
        },
        "delivery_ts": {
          "type": "date",
          "format": "epoch_millis"
        },
        "submission_ts": {
          "type": "date",
          "format": "epoch_millis"
        },
        "flight_id": {
          "type": "keyword"
        },
        "inventory": {
          "type": "keyword"
        },
        "msg_text": {
          "type": "keyword"
        },
        "nof_segments": {
          "type": "keyword"
        },
        "o_error": {
          "type": "keyword"
        },
        "recipient": {
          "type": "text"
        },
        "sender": {
          "type": "keyword"
        },
        "status": {
          "type": "keyword"
        },
        "campaign_name": {
          "type": "keyword"
        },
        "flight_name": {
          "type": "keyword"
        },
        "campaign_type": {
          "type": "keyword"
        }
      }
    }
  }
}

really appreciate if you can help .

Also i have observed the following query works fine

{"from":0,"size":10,"timeout":"300s","query":{"bool":{"must":[{"range":{"@timestamp":{"from":"2019-08-31T23:00:00.000Z","to":null,"include_lower":true,"include_upper":true,"format":"yyyy-MM-dd'T'HH:mm:ss.SSSZ","boost":1.0}}},{"range":{"@timestamp":{"from":null,"to":"2019-09-12T07:06:26.287Z","include_lower":true,"include_upper":true,"format":"yyyy-MM-dd'T'HH:mm:ss.SSSZ","boost":1.0}}},{"match":{"recipient":{"query":"420736408281","operator":"OR","prefix_length":0,"max_expansions":50,"fuzzy_transpositions":true,"lenient":false,"zero_terms_query":"NONE","auto_generate_synonyms_phrase_query":true,"boost":1.0}}},{"match":{"account_id":{"query":"a56f7e14-20f9-40e6-90c6-10604140ac5f","operator":"OR","prefix_length":0,"max_expansions":50,"fuzzy_transpositions":true,"lenient":false,"zero_terms_query":"NONE","auto_generate_synonyms_phrase_query":true,"boost":1.0}}},{"match":{"campaign_id":{"query":"6f2abca3-b46d-43f3-91be-3278a8dd7dc0","operator":"OR","prefix_length":0,"max_expansions":50,"fuzzy_transpositions":true,"lenient":false,"zero_terms_query":"NONE","auto_generate_synonyms_phrase_query":true,"boost":1.0}}},{"match":{"flight_id":{"query":"92348fa1-ca6c-456a-b3b2-85fba2d2deed","operator":"OR","prefix_length":0,"max_expansions":50,"fuzzy_transpositions":true,"lenient":false,"zero_terms_query":"NONE","auto_generate_synonyms_phrase_query":true,"boost":1.0}}},{"terms":{"status":["enroute"],"boost":1.0}},{"terms":{"inventory":["sms"],"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"sort":[{"@timestamp":{"order":"desc"}}]}

Solution

  • term queries are not analyzed and used for the exact or keyword search, in your first query, you are using the match query which is analyzed and uses the same analyzer which is used index time, hence you got the result.

    If you want to get result for your term query, please use campaign_type.keyword if it exist in your mapping(if you generated it dynamically), otherwise you need to create a keyword field to store it and query on that field.

    Please refer to elastic post explaining diff b/w term and match query.