How to implement this in elastic

Elastic Stack Elasticsearch
21

Add the range part like you did for terms inside the must part.

I forgot to say that you should replace must by filter BTW.

22

i can't get the syntax right , can you please help

GET purchase/_search
{
  "query": {
    "bool": {
      "must_not": [
        {"match": {"accttype_acct_type_code.keyword": "07"}},
        {"match": {"emp_emp_code.keyword": "9985"}}
      ],
      "filter": [
        {"terms": {"purstat_pur_status_code.keyword": ["08","09","10"]}},
        {"terms": {"plaza_plaza_id.keyword":["009500","009502"]}},
        {"terms": {"product_pur_product_code.keyword":["01","02","03","04", "05","10","11","12","13","15","16","17","18"]}},
        {"range":{
       "pur_trans_date":{
          "gte":"2015-01-01T05:00:02.000Z",
          "lte":"2015-01-01T10:00:02.000Z",
          "format":"yyyyMMdd'T'HHmmss.SSSZ"
       }}
      ]
    }
  }
}
23

What is wrong ?

An error?

24

no it does not even give me a green arrow button to run , so syntax issues.

25

this works but then I can't add the must terms to it .

GET purchase/_search
{
  "query": {
    "bool": {
      "must_not": [
        {"match": {"accttype_acct_type_code.keyword": "07"}},
        {"match": {"emp_emp_code.keyword": "9985"}}
      ],
      "filter": {
        "range":{
          "pur_trans_date":{
          "gte":"2015-01-01 05:00:02",
          "lte":"2015-01-01 10:00:02",
          "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
         }
       }
      }
    }
  }
}
26

how can I add these conditions to the above ?

{"terms": {"purstat_pur_status_code.keyword": ["08","09","10"]}},
{"terms": {"plaza_plaza_id.keyword":["009500","009502"]}},
{"terms": {"product_pur_product_code.keyword":["01","02","03","04", "05","10","11","12","13","15","16","17","18"]}}

27

Why? Does it fail?

28

ok this query is working but logically is it correct?
also its not complaining about time format .. how can I correct it?

GET purchase/_search
{
"query": {
    "bool": {
        "must": {
            "range": {
             "pur_trans_date":{
             "gte":"2016-01-01 00:00:00",
             "lte":"2016-01-01 23:59:59",
             "format": "yyyy-MM-dd HH24:mm:ss||yyyy-MM-dd||epoch_millis"
              }
            }
        },
        "must_not": [
          {"match": {"accttype_acct_type_code.keyword": "07"}},
          {"match": {"emp_emp_code.keyword": "9985"}}
        ],
        "filter": [
          {"terms": {"purstat_pur_status_code.keyword": ["08","09","10"]}},
          {"terms": {"plaza_plaza_id.keyword":["009500","009502"]}},
          {"terms": {"product_pur_product_code.keyword":["01","02","03","04", "05","10","11","12","13","15","16","17","18"]}}
        ]
        }
    }
}

}

29

error I am getting is

    "reason": "failed to parse date field [2016-01-01 00:00:00] with format [yyyy-MM-dd HH24:mm:ss||yyyy-MM-dd||epoch_millis]"
30

ok this worked , is it the right logic though of what I was trying to achieve?

GET purchase/_search
{
"query": {
    "bool": {
        "must": {
            "range": {
             "pur_trans_date":{
        	 "gte":"2016/01/01T00:00:00.000Z",
         	 "lte":"2016/01/01T23:59:59.000Z",
          	 "format":"yyyy/MM/dd'T'HH:mm:ss.SSSZ"
              }
            }
        },
        "must_not": [
          {"match": {"accttype_acct_type_code.keyword": "07"}},
          {"match": {"emp_emp_code.keyword": "9985"}}
        ],
        "filter": [
          {"terms": {"purstat_pur_status_code.keyword": ["08","09","10"]}},
          {"terms": {"plaza_plaza_id.keyword":["009500","009502"]}},
          {"terms": {"product_pur_product_code.keyword":["01","02","03","04", "05","10","11","12","13","15","16","17","18"]}}
        ]
        }
    }
}
}
2 Likes
31

ok I verified the data from the source database and it matches so query is good.
thanks a lot for your help.

32

But you should better move the range part to the filter clause

33

I will but for my learning can you explain why please?

34

Must clauses might be used in computing a score.
Filter clauses are not used to compute the score so they can be cached and run faster.

Although elasticsearch does OOTB some optimizations, I'd recommend not using must if your goal is just to filter results.

2 Likes
35

thanks . valuable info.

36

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