How to implement this in elastic

Elastic Stack Elasticsearch
1

how can I implement these sql conditions in elastic ?

select * from <index_name>
where ACCTTYPE_ACCT_TYPE_CODE != '07'
and PLAZA_PLAZA_ID in ('009500', '009502')
and emp_emp_code != '9985'
and PURSTAT_PUR_STATUS_CODE IN ( '08','09','10' )

2

You can use a bool query with some filter and must_not clauses.

1 Like
3

can you kindly give me an example for "!=" and "IN" conditions ?

4

You have a must not example here: https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-bool-query.html

For IN I'd use: https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-terms-query.html

5

I tried for example the following for "PURSTAT_PUR_STATUS_CODE IN ('08','09','10') "
but it select no rows ?

{
"query": {
"bool": {
"must": [
{"match": {"purstat_pur_status_code.keyword": "08"}},
{"match": {"purstat_pur_status_code.keyword": "09"}},
{"match": {"purstat_pur_status_code.keyword": "10"}}
]
}
}
}

6

Please format your code 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
```

Please edit your post.

7

It's because it's a AND as you wrote it.

8

I tried this but its not working , giving syntax error

GET _search
{
  "query": {
     "terms": {
       "purstat_pur_status_code.keyword": ["08","09","10"]
     },
     "bool": {"must_not": [
       {"match": {"accttype_acct_type_code.keyword": "07"}}
     ]}
   }
}
9

is the formatting ok now? I get the following error when I run the code I posted

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "[terms] malformed query, expected [END_OBJECT] but found [FIELD_NAME]",
        "line": 6,
        "col": 6
      }
    ],
    "type": "parsing_exception",
    "reason": "[terms] malformed query, expected [END_OBJECT] but found [FIELD_NAME]",
    "line": 6,
    "col": 6
  },
  "status": 400
}
10

Put the terms query inside the bool query in a must clause.

11

its a valid json but getting STATUS :400 error parsing exception

GET purchase/_search
{
  "query": {
     "bool": {"must_not": [
       {"match": {"accttype_acct_type_code.keyword": "07"}}
       ],
      "terms": {
            "purstat_pur_status_code.keyword": ["08","09","10"]
      }    
     }
   }
}
12

Look at the bool query doc I linked to.

A bool clause can only have should, must and must_not.

So put the terms inside the must clause.

Just like you did for match inside must_not.

13

is this good ?

GET purchase/_search
{
  "query": {
    "bool": {
      "must_not": [
        {"match": {"accttype_acct_type_code.keyword": "07"}}
      ],
      "must": [
        {
         "terms": {
           "purstat_pur_status_code.keyword": ["08","09","10"]
         }
        } 
      ]
    }
  }
}
14

It looks good

15

thanks for your patience , I am almost there . how do I add the new " and PLAZA_PLAZA_ID in ('009500', '009502')" term to the elastic query ? will it go in the must clause with two "terms" ?

16

Correct

17

I tried this but its giving parsing error

GET purchase/_search
{
  "query": {
    "bool": {
      "must_not": [
        {"match": {"accttype_acct_type_code.keyword": "07"}}
      ],
      "must": [
        {
         "terms": {
           "purstat_pur_status_code.keyword": ["08","09","10"]
         },
        "terms": {
           "plaza_plaza_id.keyword":["009500","009502"]
         }
        } 
      ]
    }
  }
}
18

I think I got it right . .?

GET purchase/_search
{
  "query": {
    "bool": {
      "must_not": [
        {"match": {"accttype_acct_type_code.keyword": "07"}}
      ],
      "must": [
        {"terms": {"purstat_pur_status_code.keyword": ["08","09","10"]}},
        {"terms": {"plaza_plaza_id.keyword":["009500","009502"]}}
      ]
    }
  }
}
19

I need to add a date range clause to it , how do I do that ?

 And PUR_TRANS_DATE   between  to_date('13-JAN-2015 00:00:00','dd-mon-yyyy hh24:mi:ss')  AND to_date('13-JAN-2015 23:59:59','dd-mon-yyyy hh24:mi:ss')

the following code gives me a date range but how do I add to the existing elasticsearch code?

  "query": {
     "range":{
       "pur_trans_date":{
          "gte":"2015-01-01T05:00:02.000Z",
          "lte":"2015-01-01T10:00:02.000Z",
          "format":"yyyyMMdd'T'HHmmss.SSSZ"
       }
     }
  }
20

Exact