nested select query in elasticsearch

Question

I have to convert the following query in elasticsearch :

select * from index where observable not in (select observable from index where tags = 'whitelist')

I read that I should use a Filter in a Not Filter but I don't understand how to do. Can anyone help me? Thanks

EDIT:

I have to get all except those that have 'whitelist' tag but I need to check also that nothing of the blacklist element is contained into the whitelist.

the your query should be simplified like this select * from index where tags <>'whitelist' — danvasiloiu
– danvasiloiu, Commented Jul 27, 2015 at 8:12
Yes I know but I need to check this : if a element contained with tag 'whitelist' is also present with tag <> of 'whitelist' then I'll have to exclude that. — danko
– danko, Commented Jul 27, 2015 at 8:53

Val · Accepted Answer · 2015-07-25 13:09:31Z

1

Your SQL query can be simplified to this:

select * from index where tags not in ('whitelist')

As a result the "corresponding" ES query would be

curl -XPOST localhost:9200/index/_search -d '{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must_not": {
            "terms": {
              "tags": [
                "whitelist"
              ]
            }
          }
        }
      }
    }
  }
}'

or another using the not filter instead of bool/must_not:

curl -XPOST localhost:9200/index/_search -d '{
  "query": {
    "filtered": {
      "filter": {
        "not": {
          "terms": {
            "tags": [
              "whitelist"
            ]
          }
        }
      }
    }
  }
}'

answered Jul 25, 2015 at 13:09

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

danko Over a year ago

Hi! thanks for your reply.. I have to get all except those that have 'whitelist' tag but I need to check also that nothing of the blacklist element is contained into the whitelist. So I guess that query couldn't be semplified

Val Over a year ago

Hmmm... I think the best would be to provide a sample table with 3-4 records to highlight what your query does.

Collectives™ on Stack Overflow

nested select query in elasticsearch

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related