How to use and filter or and query on the nested array

[abhishek_paraskar (abhishek.paraskar@gmail.com)]

Hello There,

I am a newbie to elasticsearch. I wanted to filter the record
by querying on multiple fields.
For example my User index is as follows -
{
"firstname": "First Name",
"lastname": "Last Name",
"privateAddress": {
"street1": "Test Streat1",
"street2": "Test Streat2",
"houseNo": "test123",
"zip": "411028",
"city": "Test City",
"country": "IN",
"contactNumbers": [
{
"number": "+11-111-1111111",
"type": "TELEPHONE"
},
{
"number": "+11-11-111-11111",
"type": "MOBILE"
}
]
},
"businessAddress": {
"addressType": "BUSINESS",
"street1": "Test Streat1",
"houseNo": "test123",
"zip": "22087",
"city": "Test City",
"country": "DE",
"contactNumbers": []
},
"websites": [
{
"website": "http: //www.dummysite.com",
"type": "COMPANY_HOMEPAGE"
},
{
"website": "http: //www.sdummysite2.com",
"type": "PERSONAL_HOMEPAGE"
},
{
"website": "http: //www.dummysite3.com",
"type": "PERSONAL_BLOG"
},
{
"website": "http: //www.dummysite4.com",
"type": "PERSONAL_HOMEPAGE"
}
],
"experiences": [
{
"current": false,
"organization": "Test Organization",
"position": "Test Position",
"industry": "Test Industry",
"otherIndustries": "Test Other Industry",
"occupationStatus": "Test Occupation Status",
},
{
"current": true,
"organization": "Test Organization2",
"position": "Test Position2",
"industry": "Test Industry 2",
"otherIndustries": "Test Other Industry 2",
"occupationStatus": "Test Occupation Status2",
}
]
}

Now here I need the query something like this :

Select * from User where user.firstname = "First Name" &&
user.lastname="Last Name" && user.experiences.organization = "Test
Organization2" && user.experiences.current = true;

I have achieved First name and last name query by using bool filter (with
the help of query string).

The query is something like -

{
"bool" : {
"must" : [ {
"query_string" : {
"query" : "first name",
"default_field" : "User.firstname",
"default_operator" : "and"
}
}, {
"query_string" : {
"query" : "last name",
"default_field" : "User.lastname",
"default_operator" : "and"
}
}
} ]
}
}

With this query I get the result proper. But when I try to add the query
for experience like -

{
"bool" : {
"must" : [ {
"query_string" : {
"query" : "first name",
"default_field" : "User.firstname",
"default_operator" : "and"
}
}, {
"query_string" : {
"query" : "last name",
"default_field" : "User.lastname",
"default_operator" : "and"
}
}, {
"bool" : {
"must" : [ {
"query_string" : {
"query" : "Test Organization",
"default_field" : "User.experiences.organization",
"default_operator" : "and"
}
}, {
"query_string" : {
"query" : "true",
"default_field" : "User.experiences.current",
"default_operator" : "and"
}
} ]
}
}
} ]
}
}

It returns me the same user record.
Here we should not that -

1. The experiences.current field is of type Boolean.
2. In the query I have passed experiences.current = "true" (Here I don't
   know how to pass value for boolean field in query) and
   experiences.organization = "Test Organization"

As if you will observe the user object, it does not contain
the experiences.current = "true" && experiences.organization = "Test
Organization" under the experiences.
But still here, the es is returning the same user object where as the
expected result should be 0.

So my question here is -
How I should query the elasticsearch so that I will get the correct result
for experience.current and experience.organization conditions ???

I know that I am missing something here. Please guide me...
Thanks in advance.

--

[mvg (Martijn Van Groningen)]

Hi,

You have to use the nested type for fields in your mapping that are
basically objects arrays:

Elasticsearch Platform — Find real-time answers at scale

The reason why you need to do this is that the inverted index is flat
while the json data isn't. This fact is only noticeable if fields with
object arrays appear in the json data. If the nested type is used,
then under the hood we can leverage a nice Lucene trick and work
around this issue.

On top of this you need to use the nested filter or query in your queries:

Elasticsearch Platform — Find real-time answers at scale

Elasticsearch Platform — Find real-time answers at scale

Martijn

On 20 December 2012 11:23, Abhishek Paraskar
abhishek.paraskar@gmail.com wrote:

Hello There,

I am a newbie to elasticsearch. I wanted to filter the record by querying on
multiple fields.
For example my User index is as follows -
{
"firstname": "First Name",
"lastname": "Last Name",
"privateAddress": {
"street1": "Test Streat1",
"street2": "Test Streat2",
"houseNo": "test123",
"zip": "411028",
"city": "Test City",
"country": "IN",
"contactNumbers": [
{
"number": "+11-111-1111111",
"type": "TELEPHONE"
},
{
"number": "+11-11-111-11111",
"type": "MOBILE"
}
]
},
"businessAddress": {
"addressType": "BUSINESS",
"street1": "Test Streat1",
"houseNo": "test123",
"zip": "22087",
"city": "Test City",
"country": "DE",
"contactNumbers":
},
"websites": [
{
"website": "http: //www.dummysite.com",
"type": "COMPANY_HOMEPAGE"
},
{
"website": "http: //www.sdummysite2.com",
"type": "PERSONAL_HOMEPAGE"
},
{
"website": "http: //www.dummysite3.com",
"type": "PERSONAL_BLOG"
},
{
"website": "http: //www.dummysite4.com",
"type": "PERSONAL_HOMEPAGE"
}
],
"experiences": [
{
"current": false,
"organization": "Test Organization",
"position": "Test Position",
"industry": "Test Industry",
"otherIndustries": "Test Other Industry",
"occupationStatus": "Test Occupation Status",
},
{
"current": true,
"organization": "Test Organization2",
"position": "Test Position2",
"industry": "Test Industry 2",
"otherIndustries": "Test Other Industry 2",
"occupationStatus": "Test Occupation Status2",
}
]
}

Now here I need the query something like this :

Select * from User where user.firstname = "First Name" &&
user.lastname="Last Name" && user.experiences.organization = "Test
Organization2" && user.experiences.current = true;

I have achieved First name and last name query by using bool filter (with
the help of query string).

The query is something like -

{
"bool" : {
"must" : [ {
"query_string" : {
"query" : "first name",
"default_field" : "User.firstname",
"default_operator" : "and"
}
}, {
"query_string" : {
"query" : "last name",
"default_field" : "User.lastname",
"default_operator" : "and"
}
}
} ]
}
}

With this query I get the result proper. But when I try to add the query for
experience like -

{
"bool" : {
"must" : [ {
"query_string" : {
"query" : "first name",
"default_field" : "User.firstname",
"default_operator" : "and"
}
}, {
"query_string" : {
"query" : "last name",
"default_field" : "User.lastname",
"default_operator" : "and"
}
}, {
"bool" : {
"must" : [ {
"query_string" : {
"query" : "Test Organization",
"default_field" : "User.experiences.organization",
"default_operator" : "and"
}
}, {
"query_string" : {
"query" : "true",
"default_field" : "User.experiences.current",
"default_operator" : "and"
}
} ]
}
}
} ]
}
}

It returns me the same user record.
Here we should not that -

1. The experiences.current field is of type Boolean.
2. In the query I have passed experiences.current = "true" (Here I don't
   know how to pass value for boolean field in query) and
   experiences.organization = "Test Organization"

As if you will observe the user object, it does not contain the
experiences.current = "true" && experiences.organization = "Test
Organization" under the experiences.
But still here, the es is returning the same user object where as the
expected result should be 0.

So my question here is -
How I should query the elasticsearch so that I will get the correct result
for experience.current and experience.organization conditions ???

I know that I am missing something here. Please guide me...
Thanks in advance.

--

--
Met vriendelijke groet,

Martijn van Groningen

--