Not able to search with date range filter

Elastic Stack Elasticsearch
1

I had integrated Elasticsearch in my ruby on rails project with chewy gem.
All the searches are working fine except date range filter I tried every approach but not getting any error or response is always blank array that means query is correct and it's not even working for exact search.

2

What is the mapping for the field you are date range filtering on? What does your date range filter/clause look like? Which version of Elasticsearch are you using?

3

Elasticsearch gem version:- 7.2.0

Indexing of created at field in users index is:-
field :created_at, type: 'date', format: "yyyy-MM-dd HH:mm:ss||strict_date_optional_time ||epoch_millis"

4

Please get the mapping from the get field mapping API so we know what is actually in Elasticsearch.

What does the query look like?

5

I'm using chewy gem so do I need to run raw query also ?

6

I tried curl request and through chewy gem both are not responding.

  1. curl -XPOST "http://3.143.106.232:9202/users/_search" -H 'Content-Type: application/json' -d'{"query": {"bool": {"term": {"created_at": "2023-01-02T06:10:15.000Z"}}}}'

  2. curl -XPOST "http://3.143.106.232:9202/users/_search" -H 'Content-Type: application/json' -d'{"query": {"range": {"created_at": {"gte": "2021-09-16T14:06:02", "lt": "2023-09-16T14:06:02"}}}}'

  3. with chewy gem:-
    from_date = '2023-09-11T00:00:00Z' # ISO 8601 format with time and timezone
    to_date = '2023-10-11T23:59:59Z' # ISO 8601 format with time and timezone
    def search_with_date_range(from_date, to_date)

Use Chewy's DSL to construct the search request

query = { bool: { filter: [{ range: { 'created_at': { gte: from_date, lte: to_date } } }] } }

Execute the search

UsersIndex.query(query)
end

7

Run a query directly against Elasticsearch. Use curl or some other tool, e,g Kibana dev tools console.

8

I just share two curl commands with you is there any issue in those ?

9

What is the mapping of the field?

Are you getting no response at all or an empty response?

10

I'm getting empty response.

11

Please show me the mapping of the field as well as a document in the index that you expect to find.

12

{"_index":"users","_id":"1139","_score":1.0,"_source":{"first_name":"bchjdshs","last_name":"vrerh","state":"pending","phone_number":"","username":"unntvvvvv","email":"mukul.jaiswa@dsdf.com","uid":"ID64A5F99228","role":"member","platform":null,"referral_id":null,"id":1139,"country":null,"level":0,"users_count":0,"created_at":"2023-01-02T06:10:15.000Z"}}

13

Attaching my index file:-

frozen_string_literal: true

class UsersIndex < Chewy::Index
settings analysis: {
analyzer: {
email: {
tokenizer: 'keyword',
type: 'keyword'
}
}
}

index_scope User
root date_detection: true do
field :first_name
field :last_name
field :state
field :phone_number
field :username
field :email, analyzer: 'email'
field :uid, type: 'keyword'
field :role
field :platform
field :referral_id, type: 'integer'
field :id, type: 'integer'
field :country
field :level, type: 'integer'
field :users_count, type: 'integer'
field :created_at, type: 'date', format: "yyyy-MM-dd HH:mm:ss||strict_date_optional_time ||epoch_millis"
end
end

14

I want to see what the mapping in Elasticsearch is, not what your code thinks it has set it to.

15

here it is:-
{"users":{"mappings":{"dynamic":"false","properties":{"country":{"type":"text","analyzer":"keyword"},"email":{"type":"text","analyzer":"keyword"},"first_name":{"type":"text"},"id":{"type":"integer"},"last_name":{"type":"text"},"level":{"type":"integer"},"phone_number":{"type":"text"},"platform":{"type":"text"},"referral_id":{"type":"integer"},"role":{"type":"text"},"state":{"type":"text"},"uid":{"type":"keyword"},"username":{"type":"text"},"users_count":{"type":"integer"}}}}}

16

we don't have created_at here in the mapping but we have this field in the response of match_all query.

17

What is the full output of curl -XGET "http://3.143.106.232:9202/users/_mapping" ?

18

this one:-
{"users":{"mappings":{"dynamic":"false","properties":{"country":{"type":"text","analyzer":"keyword"},"email":{"type":"text","analyzer":"keyword"},"first_name":{"type":"text"},"id":{"type":"integer"},"last_name":{"type":"text"},"level":{"type":"integer"},"phone_number":{"type":"text"},"platform":{"type":"text"},"referral_id":{"type":"integer"},"role":{"type":"text"},"state":{"type":"text"},"uid":{"type":"keyword"},"username":{"type":"text"},"users_count":{"type":"integer"}}}}}

19

Well, if the created_at field is not in the mappings, it has not been indexed and can therefore not be searched. That would explain why you are not getting any hits. I think you need to recreate the index and ensure all fields are correctly mapped.

20

this is the manual index which I had created before and then I switched to a different gem and removed this but the index is not getting changed even after indexing data after deleting the existing index.