# Complex AND/OR filters
**URL:** https://discuss.elastic.co/t/complex-and-or-filters/7641
**Category:** Elasticsearch
**Created:** [May 10, 2012, 6:02pm UTC](https://discuss.elastic.co/t/complex-and-or-filters/7641 "2012-05-10T18:02:53Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Spiderman](https://discuss.elastic.co/u/Spiderman)
#### Post date: [May 10, 2012, 6:02pm UTC](https://discuss.elastic.co/t/complex-and-or-filters/7641/1 "2012-05-10T18:02:53Z")
Hi,
I have a question with regards to a complex OR and AND query. I have
been googling it all day with no luck.
Here is a sample data for an index called "contacts".
company\_id, contact\_id, contact\_type name
1 6 Company Amazon…..
6 14 Person
Amazon…..
1 14 Person
Amazon…..
6 15 Person
Amazon…..
1 15 Person
Amazon…..
6 16 Person
Amazon…..
1 16 Person
Amazon…..
Now say I want to query for name with "amazon\*" but filtered on say
(company\_id = 6 AND contact\_id = 14)
OR
(contact\_type = "Company")
i.e. this should return
1 6 Company Amazon…..
6 14 Person
Amazon…..
this is what my code looks like:
curl -X GET "[http://localhost:9200/contact\_contacts/\_search](http://localhost:9200/contact_contacts/_search)?
pretty=true" -d '
{
"query": {
"bool": {
"should": [
{
"query\_string":
{
"query":"amazon\*","default\_operator":"AND"
}
}
]
}
},
"filter": {
"or" : [
{
"term" : {"contact\_type" : "Company::Company"}
},
{
"term" : {"contact\_id" : "14", "company\_id" : "6"}
}
]
}
}'
But the above returns
1 6 Company Amazon…..
6 14 Person
Amazon…..
in addition to the following which i do not want.
6 15 Person
Amazon…..
6 16 Person
Amazon…..
How would I go about constructing the filters? I am hoping you can
help me out on this one.
thank you
Prabakar Puvanathasan
---
### Author:  [@Spiderman](https://discuss.elastic.co/u/Spiderman)
#### Post date: [May 10, 2012, 8:50pm UTC](https://discuss.elastic.co/t/complex-and-or-filters/7641/2 "2012-05-10T20:50:17Z")
Okay I figured it out. Here is the query. Have to nest the AND inside the
OR.
curl -X GET "[http://localhost:9200/contact\_contacts/\_search?pretty=true](http://localhost:9200/contact_contacts/_search?pretty=true)" -d
'
{
"query": {
"bool": {
"should": [
{
"query\_string":
{
"query":"amazon\*","default\_operator":"AND"
}
}
]
}
},
"filter": {
"or" : [
{
"term" : {"contact\_type" : "Company::Company"}
},
{
"and" : [
{
"term" : {"contact\_id" : "14"}
},
{
"term" : {"company\_id" : "6"}
}
]
}
]
}
}'
On Thursday, May 10, 2012 2:02:53 PM UTC-4, Prabakar Puvanathasan wrote:
> Hi,
>
> I have a question with regards to a complex OR and AND query. I have
> been googling it all day with no luck.
>
> Here is a sample data for an index called "contacts".
>
> company\_id, contact\_id, contact\_type name
> 1 6 Company Amazon…..
> 6 14 Person
> Amazon…..
> 1 14 Person
> Amazon…..
> 6 15 Person
> Amazon…..
> 1 15 Person
> Amazon…..
> 6 16 Person
> Amazon…..
> 1 16 Person
> Amazon…..
>
> Now say I want to query for name with "amazon\*" but filtered on say
>
> (company\_id = 6 AND contact\_id = 14)
> OR
> (contact\_type = "Company")
>
> i.e. this should return
> 1 6 Company Amazon…..
> 6 14 Person
> Amazon…..
>
> this is what my code looks like:
>
> curl -X GET "[http://localhost:9200/contact\_contacts/\_search](http://localhost:9200/contact_contacts/_search)?
> pretty=true [http://localhost:9200/contact\_contacts/\_search?pretty=true](http://localhost:9200/contact_contacts/_search?pretty=true)"
> -d '
> {
> "query": {
> "bool": {
> "should": [
> {
> "query\_string":
> {
> "query":"amazon\*","default\_operator":"AND"
> }
> }
> ]
> }
> },
>
> "filter": {
> "or" : [
> {
> "term" : {"contact\_type" : "Company::Company"}
> },
> {
> "term" : {"contact\_id" : "14", "company\_id" : "6"}
> }
> ]
> }
> }'
>
> But the above returns
>
> 1 6 Company Amazon…..
> 6 14 Person
> Amazon…..
> in addition to the following which i do not want.
> 6 15 Person
> Amazon…..
> 6 16 Person
> Amazon…..
>
> How would I go about constructing the filters? I am hoping you can
> help me out on this one.
>
> thank you
> Prabakar Puvanathasan
---
### Author:  [@andym](https://discuss.elastic.co/u/andym)
#### Post date: [May 10, 2012, 8:51pm UTC](https://discuss.elastic.co/t/complex-and-or-filters/7641/3 "2012-05-10T20:51:00Z")
Try "filtered" query
> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.
here's example with "AND"
{
"query": {
"filtered": {
"query": {
"query\_string": {
"query": [
"amazon"
]
}
},
"filter": {
"and": [
{
"term": {
"contact\_type": [
"XYZ"
]
}
}
]
}
}
},
On May 10, 2:02 pm, Spiderman [prab...@gmail.com](mailto:prab...@gmail.com) wrote:
> Hi,
>
> I have a question with regards to a complex OR and AND query. I have
> been googling it all day with no luck.
>
> Here is a sample data for an index called "contacts".
>
> company\_id, contact\_id, contact\_type name
> 1 6 Company Amazon…..
> 6 14 Person
> Amazon…..
> 1 14 Person
> Amazon…..
> 6 15 Person
> Amazon…..
> 1 15 Person
> Amazon…..
> 6 16 Person
> Amazon…..
> 1 16 Person
> Amazon…..
>
> Now say I want to query for name with "amazon\*" but filtered on say
>
> (company\_id = 6 AND contact\_id = 14)
> OR
> (contact\_type = "Company")
>
> i.e. this should return
> 1 6 Company Amazon…..
> 6 14 Person
> Amazon…..
>
> this is what my code looks like:
>
> curl -X GET "[http://localhost:9200/contact\_contacts/\_search](http://localhost:9200/contact_contacts/_search)?
> pretty=true" -d '
> {
> "query": {
> "bool": {
> "should": [
> {
> "query\_string":
> {
> "query":"amazon\*","default\_operator":"AND"
> }
> }
> ]
> }
> },
>
> "filter": {
> "or" : [
> {
> "term" : {"contact\_type" : "Company::Company"}
> },
> {
> "term" : {"contact\_id" : "14", "company\_id" : "6"}
> }
> ]
> }
>
> }'
>
> But the above returns
>
> 1 6 Company Amazon…..
> 6 14 Person
> Amazon…..
> in addition to the following which i do not want.
> 6 15 Person
> Amazon…..
> 6 16 Person
> Amazon…..
>
> How would I go about constructing the filters? I am hoping you can
> help me out on this one.
>
> thank you
> Prabakar Puvanathasan
---
### Author:  [@drewr](https://discuss.elastic.co/u/drewr)
#### Post date: [May 10, 2012, 10:32pm UTC](https://discuss.elastic.co/t/complex-and-or-filters/7641/4 "2012-05-10T22:32:31Z")
See below.
Spiderman wrote:
[...]
> Now say I want to query for name with "amazon\*" but filtered on say
>
> (company\_id = 6 AND contact\_id = 14)
> OR
> (contact\_type = "Company")
>
> i.e. this should return
> 1 6 Company Amazon…..
> 6 14 Person
> Amazon…..
>
> this is what my code looks like:
>
> curl -X GET "[http://localhost:9200/contact\_contacts/\_search](http://localhost:9200/contact_contacts/_search)?
> pretty=true" -d '
> {
> "query": {
> "bool": {
> "should": [
> {
> "query\_string":
> {
> "query":"amazon\*","default\_operator":"AND"
> }
> }
> ]
> }
> },
>
> "filter": {
> "or" : [
> {
> "term" : {"contact\_type" : "Company::Company"}
> },
> {
[...]
> ```
> "term" : {"contact_id" : "14", "company_id" : "6"}
>
> ```
This is responsible for the extra docs.
[...]
> How would I go about constructing the filters? I am hoping you can
> help me out on this one.
I would go about it this way:
```
curl -s localhost:9200/test/_search\?pretty=1 -d'
{
"query": {
"filtered": {
"filter": {
"or": [
{
"term": {
"contact_type": "company"
}
},
{
"and": [
{
"term": {
"contact_id": 14
}
},
{
"term": {
"company_id": 6
}
}
]
}
]
},
"query": {
"prefix": {
"name": "amazon"
}
}
}
}
}
'; echo
{
"took" : 4,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 2,
"max_score" : 1.0,
"hits" : [ {
"_index" : "test",
"_type" : "foo",
"_id" : "a",
"_score" : 1.0, "_source" :
{"company_id":1,"contact_id":6,"contact_type":"Company","name":"Amazon"}
}, {
"_index" : "test",
"_type" : "foo",
"_id" : "b",
"_score" : 1.0, "_source" :
{"company_id":6,"contact_id":14,"contact_type":"Person","name":"Amazon"}
} ]
}
}
```
-Drew
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 3:29am UTC](https://discuss.elastic.co/t/complex-and-or-filters/7641/5 "2017-07-06T03:29:20Z")