Complex AND/OR filters

Spiderman · May 10, 2012, 6:02pm

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?
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

Spiderman · May 10, 2012, 8:50pm

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" -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?
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

andym · May 10, 2012, 8:51pm

Try "filtered" query

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 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?
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

drewr · May 10, 2012, 10:32pm

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?
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