Terms query is not working in Elasticsearch 7.1.0 and 7.2.0

KRISHNA_SINHA · July 10, 2019, 11:56am

The terms query does not give any output. However, it does not give any error message as well.

Sample Query
GET filebeat*/_search
{
"query" : {
"bool": {
"must": [
{"terms": {
"model": ["CM5","(null)"]
}
}
]

}
}

The output is
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 0,
"relation" : "eq"
},
"max_score" : null,
"hits" :
}
}

Whereas, there are hundreds of records where model is either "CM5" or (null).

The same query was working fine in elasticsearch 6.x.x

dadoonet · July 10, 2019, 12:16pm

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

KRISHNA_SINHA · July 10, 2019, 12:27pm

Hi David,
I am not sure if I have understood your question properly.
If I use the following query

GET filebeat*/_search
{
"query": {
"match_phrase": {
"model": "CM5"
}
}
}

Then, I get 973 records.
"took" : 119,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 973,
"relation" : "eq"
},

If I try same thing using terms query, then I get no record.

GET filebeat*/_search
{
"query": {
"terms": {
"model": [
"CM5"
]
}
}
}

Output is given below
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 0,
"relation" : "eq"
},
"max_score" : null,
"hits" :
}
}

KRISHNA_SINHA · July 10, 2019, 12:32pm

GET filebeat*/_search

{
"query": {
"terms": {
"model": [
"CM5"
]
}
}
}

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

dadoonet · July 10, 2019, 12:55pm

What I need is an example which reproduces your problem.

But if you can't give any, let's guess...

Try with cm5 or cm.

KRISHNA_SINHA · July 10, 2019, 2:17pm

Great!!! It worked with cm5. But the actual data is in CAPS (like CM5). Why is searching CM5 when I give search criteria as cm5 and not as CM5?

KRISHNA_SINHA · July 10, 2019, 2:21pm

Now the new query is

GET filebeat*/_search

{
"query": {
"terms": {
"model": [
"cm5"
]
}
}
}

and the output is as below

{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 973,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "filebeat-7.1.0-2019.07.08-000001",
"_type" : "_doc",
"id" : "m7Jf2msBn8IJC3PDKTB",
"_score" : 1.0,
"_source" : {
"@timestamp" : "2019-07-10T05:33:17.040Z",
"Component" : "ImportExportService",
"serialnumber" : "(null)",
"log" : {
"offset" : 0,
"file" : {
"path" : """C:\vhq-core\Server\ImportExportService\VHQImportExportService\bin\Debug\importexportservice-10.07.2019 - Copy.log"""
}
},
"method" : "Start",
"Application" : "VHQ NA",
"deviceid" : "(null)",
"model" : "CM5",
"correlationid" : "(null)",
"HostName" : "blr2krishnas3",
"deviceuid" : "(null)",
"EntryDate" : "2019-07-10T08:38:20.20+05:30",
"message" : """Message: Unable to connect to the remote server. Call: Status code unknown from: PUT /_template/filebeat-7.1.0?pretty=true&error_trace=trueStackTrace: at Elasticsearch.Net.Transport1.HandleElasticsearchClientException(RequestData data, Exception clientException, IElasticsearchResponse response) at Elasticsearch.Net.Transport1.FinalizeResponse[TResponse](RequestData requestData, IRequestPipeline pipeline, List1 seenExceptions, TResponse response) at Elasticsearch.Net.Transport1.Request[TResponse](HttpMethod method, String path, PostData data, IRequestParameters requestParameters) at Nest.Specification.IndicesApi.IndicesNamespace.PutTemplate(Name name, Func`2 selector) at VHQCommon.CommonMethods.SetDateDetection() in C:\vhq-core\Common\VHQCommon\CommonMethods.cs:line 237 at VHQImportExportServiceLib.VHQImportExportManager.Start() in C:\vhq-core\Server\ImportExportService\VHQImportExportServiceLib\VHQImportExportManager.cs:line 60Inner exception Message: Unable to connect to the remote serverInner exception StackTrace: at System.Net.HttpWebRequest.GetRequestStream(TransportContext& context) at System.Net.HttpWebRequest.GetRequestStream() at Elasticsearch.Net.HttpWebRequestConnection.Request[TResponse](RequestData requestData)Inner exception Message: No connection could be made because the target machine actively refused it 127.0.0.1:9200Inner exception StackTrace: at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)""",
"host" : {
"name" : "blr2krishnas3"
},
"classname" : "VHQImportExportServiceLib.VHQImportExportManager",
"username" : "(null)",
"customername" : "(null)",
"level" : "ERROR",
"input" : {
"type" : "log"
},
"ecs" : {
"version" : "1.0.0"
},
"agent" : {
"id" : "ae429de5-5758-46fc-9994-775052020e54",
"version" : "7.1.0",
"type" : "filebeat",
"ephemeral_id" : "5781c9ca-d544-4287-8ed1-e5a3af650b53",
"hostname" : "blr2krishnas3"
}
}
},

In the output the value of model is CM5 and not cm5. I wanted to have a case sensitive search here. So, if the query has search criteria as "model":"CM5", it should search only those models which is exactly CM5 and not cm5 or Cm5 or cM5. How can I achieve this in Elasticsearch 7.1.0. I did not do anything special in 6.x.x and it was working as expected.

dadoonet · July 10, 2019, 3:16pm

Please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.

Or use markdown style like:

```
CODE
```

This is the icon to use if you are not using markdown format:

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.
Please update your post.

Because you are using a term query on a text field which is analyzed at index time and probably transform to cm5. CM5 is not cm5 that's why a term query can't find CM5.
You need to change the mapping of you want to make it work with CM5.

KRISHNA_SINHA · July 10, 2019, 3:21pm

My current mapping as as below

 "filebeat-7.1.0-2019.07.08-000001" : {
"mappings" : {
  "date_detection" : true,
  "properties" : {
    "@timestamp" : {
      "type" : "date"
    },
    "Application" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "Component" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "EntryDate" : {
      "type" : "date"
    },
    "HostName" : {
      "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
            }
          }
        },
        "type" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "version" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    },
    "classname" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "correlationid" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "customername" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "deviceid" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "deviceuid" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "ecs" : {
      "properties" : {
        "version" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    },
    "error" : {
      "properties" : {
        "message" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "type" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    },
    "host" : {
      "properties" : {
        "name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    },
    "input" : {
      "properties" : {
        "type" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    },
    "level" : {
      "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"
        }
      }
    },
    "message" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "method" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "model" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "serialnumber" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "username" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    }
  }
}

},

Can you please refer me some URL/document which can give me more details about what change I should do in the mapping so that term query does not change CM5 to cm5.

dadoonet · July 10, 2019, 4:24pm

As I asked here: Terms query is not working in Elasticsearch 7.1.0 and 7.2.0, if you provide a ready to use script on which we can then iterate so you understand better how elasticsearch works actually.

Anyway, I had some free minutes so I built it for you. It does not mean that I'll do it again in the future but at least you know now what we are expecting from you when you are asking for help.
The sooner you provide a complete and simple example the faster you'll get the right answer. And please format your code.

DELETE test
PUT test
{
  "mappings": {
    "properties": {
      "model": {
        "type": "text"
      },
      "model_untouched": {
        "type": "keyword"
      }

    }
  }
}

PUT test/_doc/1
{
  "model": "MD5",
  "model_untouched": "MD5"
}
GET test/_search
{
  "query": {
    "term": {
      "model": {
        "value": "MD5"
      }
    }
  }
}
GET test/_search
{
  "query": {
    "term": {
      "model": {
        "value": "md5"
      }
    }
  }
}
GET test/_search
{
  "query": {
    "term": {
      "model_untouched": {
        "value": "MD5"
      }
    }
  }
}
GET test/_search
{
  "query": {
    "term": {
      "model_untouched": {
        "value": "md5"
      }
    }
  }
}

KRISHNA_SINHA · July 10, 2019, 5:17pm

Thanks a lot for your help. It worked!!!

system · August 7, 2019, 5:17pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Term Query is not working Elasticsearch	8	1445	December 26, 2018
Terms query not working for me Elasticsearch	5	4235	June 15, 2018
Term query not working Elasticsearch	1	317	October 17, 2019
Query Terms = NULL or [] Elasticsearch	2	235	May 9, 2019
Elasticsearch query Elasticsearch	6	759	December 16, 2021

Terms query is not working in Elasticsearch 7.1.0 and 7.2.0

Related topics