# How to use and filter or and query on the nested array **URL:** \ **Category:** Elasticsearch\ **Created:** [December 20, 2012, 10:23am UTC](https://discuss.elastic.co/t/how-to-use-and-filter-or-and-query-on-the-nested-array/10133 "2012-12-20T10:23:02Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![abhishek\_paraskar](https://avatars.discourse-cdn.com/v4/letter/a/2acd7d/32.png) [@abhishek\_paraskar](https://discuss.elastic.co/u/abhishek_paraskar)\ **Post date:** [December 20, 2012, 10:23am UTC](https://discuss.elastic.co/t/how-to-use-and-filter-or-and-query-on-the-nested-array/10133/1 "2012-12-20T10:23:02Z")
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](https://www.dummysite.com)", "type": "COMPANY\_HOMEPAGE" }, { "website": "http: [//www.sdummysite2.com](https://www.sdummysite2.com)", "type": "PERSONAL\_HOMEPAGE" }, { "website": "http: [//www.dummysite3.com](https://www.dummysite3.com)", "type": "PERSONAL\_BLOG" }, { "website": "http: [//www.dummysite4.com](https://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. -- ---
**Author:** ![mvg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mvg/32/98890_2.png) [@mvg](https://discuss.elastic.co/u/mvg)\ **Post date:** [December 20, 2012, 11:01am UTC](https://discuss.elastic.co/t/how-to-use-and-filter-or-and-query-on-the-nested-array/10133/2 "2012-12-20T11:01:38Z")
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](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. 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](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. > **[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. Martijn On 20 December 2012 11:23, Abhishek Paraskar [abhishek.paraskar@gmail.com](mailto: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](https://www.dummysite.com)", > "type": "COMPANY\_HOMEPAGE" > }, > { > "website": "http: [//www.sdummysite2.com](https://www.sdummysite2.com)", > "type": "PERSONAL\_HOMEPAGE" > }, > { > "website": "http: [//www.dummysite3.com](https://www.dummysite3.com)", > "type": "PERSONAL\_BLOG" > }, > { > "website": "http: [//www.dummysite4.com](https://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 -- ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:59am UTC](https://discuss.elastic.co/t/how-to-use-and-filter-or-and-query-on-the-nested-array/10133/3 "2017-07-06T02:59:08Z")