# Query not working as expected
**URL:** https://discuss.elastic.co/t/query-not-working-as-expected/343186
**Category:** Elasticsearch
**Created:** [September 16, 2023, 10:23pm UTC](https://discuss.elastic.co/t/query-not-working-as-expected/343186 "2023-09-16T22:23:50Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@DWAIPAYAN\_SOM](https://discuss.elastic.co/u/DWAIPAYAN_SOM)
#### Post date: [September 16, 2023, 10:23pm UTC](https://discuss.elastic.co/t/query-not-working-as-expected/343186/1 "2023-09-16T22:23:50Z")
The below are my query for selection and rejection.
Selection :
```auto
{
"nested": {
"path": "somethingnew",
"query": {
"bool": {
"must": [
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"somethingnew.workexp"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
},
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"somethingnew.summary"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
},
{
"match": {
"somethingnew.uniqueid": {
"query": "FH-SAM-3"
}
}
}
]
}
}
}
}
```
Rejection Query
```auto
{
"nested": {
"path": "applications",
"query": {
"bool": {
"must": [
{
"match": {
"applications.forreqid": {
"query": "FH-REQ-3"
}
}
}
],
"must_not": [
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"applications.workexp"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
},
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"applications.summary"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
}
]
}
}
}
}
```
The query doesnot return any results even when there are value in "summary" and workexp field
---
### Author:  [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)
#### Post date: [September 18, 2023, 9:47am UTC](https://discuss.elastic.co/t/query-not-working-as-expected/343186/2 "2023-09-18T09:47:57Z")
Hi @DWAIPAYAN_SOM,
Can you share your document mapping and an example document that you expect to be returned by your query?
---
### Author:  [@DWAIPAYAN\_SOM](https://discuss.elastic.co/u/DWAIPAYAN_SOM)
#### Post date: [September 18, 2023, 11:52am UTC](https://discuss.elastic.co/t/query-not-working-as-expected/343186/3 "2023-09-18T11:52:04Z")
Hi @carly.richmond ,
Thank you for having a look
Note : This query is being resolved in stack overflow.
The problem was since I am using simple\_query\_string and expecting Operator.Or to behave as boolean operator this was the issue. The solution was to use " | " instead of "Or" or else use query\_string instead of simple\_query\_string .
But the must\_not does not behave the way as expected. I have now converted it to must and then n the query as (NOT (Tableau) AND NOT (Tableau WorkBooks). In case you can help me out there would be great.
Query 1
```auto
{
"nested": {
"path": "applications",
"query": {
"bool": {
"must": [
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"applications.workexp"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
},
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"applications.summary"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
},
{
"match": {
"applications.forreqid": {
"query": "FH-REQ-3"
}
}
}
]
}
}
}
}
```
Query 2
```auto
{
"nested": {
"path": "applications",
"query": {
"bool": {
"must": [
{
"match": {
"applications.forreqid": {
"query": "FH-REQ-3"
}
}
}
],
"must_not": [
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"applications.workexp"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
},
{
"simple_query_string": {
"default_operator": "and",
"fields": [
"applications.summary"
],
"query": "(Tableau) Or (Tableau) Or (Tableau workbooks)"
}
}
]
}
}
}
}
```
Document/ Row in the index canidature
```auto
{
createdby : abc@soething.com,
applications : [
{
applnid : yy,
summary : top and Tableau Server.· Involved in dashboard test cases creation and execution, prepared the understanding and function/ process flow documents on various dashboards for the end users.· Created technical specifications document as well as functional documents in support of the user requirements.· Generate Tableau reports to analyze data from multiple data sources like Oracle, SQL Server, Excel, Flat Files, etc · Experienced in designing customized interactive dashboards in Tableau using Marks, Action, filters, parameter and calculations · Having experience in Tableau Desktop Creating Sets, Group, Sort, Parameter, Quick filters, Context Filters, Data blending, Joins and Calculations etc.Experience Details · Educational Detai,
workexp : Procurement of billing the project and Project lead · The solution involves in creating dashboards and stories that depict different levels and stages.· The very first is a managerial dashboard to give quick overview of the Categories and their overview among different geographical areas, trends and comparisons using Map Charts, Pies, Stacked Bars, Scatter Plots etc.· The second one concentrates more on slicing and dicing the inventory and sales data using Dual Axis Charts, Various line charts, Waterfall charts etc.· The last one is a blend of Calculated Fields, Table Calculations and a bit of LODs to answer different types of the requirements of the client. Role : Tableau Developer Revenue Growth in % · Used Filters to know Department wise Sales and their Cost for Particular Periods and draft various charts using Show Me in Tableau Desktop.,
education : null,
certtrainings : null,
gaps : null,
skilltags : null,
forreqid : FH-REQ-3,
uploadedbyuser : null
}
]
}
```
Java Query formation using co.elastic dsl
```auto
Query workExQuery = SimpleQueryStringQuery.of(q -> q.query(finalQuery)
.fields(Arrays.asList("applications.workexp")).defaultOperator(Operator.And))._toQuery();
queries.add(workExQuery);
Query summaryQuery = SimpleQueryStringQuery.of(q -> q.query(finalQuery)
.fields(Arrays.asList("applications.summary")).defaultOperator(Operator.And))._toQuery();
queries.add(summaryQuery);
```
Mapping from elastic Curl
```auto
{
"candidature": {
"aliases": {},
"mappings": {
"properties": {
"_class": {
"type": "keyword",
"index": false,
"doc_values": false
},
"applications": {
"type": "nested",
"include_in_parent": true,
"properties": {
"_class": {
"type": "keyword",
"index": false,
"doc_values": false
},
"applnid": {
"type": "keyword"
},
"certtrainings": {
"type": "text"
},
"education": {
"type": "text"
},
"employers": {
"type": "text"
},
"forreqid": {
"type": "keyword"
},
"gaps": {
"type": "integer"
},
"skills": {
"type": "text"
},
"skilltags": {
"type": "text"
},
"summary": {
"type": "text"
},
"totalexperienceinyears": {
"type": "integer"
},
"uploadedbyuser": {
"type": "keyword"
},
"workexp": {
"type": "text"
}
}
},
"candidateId": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"contactnumber": {
"type": "keyword"
},
"createdby": {
"type": "keyword"
},
"email": {
"type": "keyword"
},
"fororg": {
"type": "keyword"
},
"name": {
"type": "text"
}
}
},
"settings": {
"index": {
"routing": {
"allocation": {
"include": {
"_tier_preference": "data_content"
}
}
},
"refresh_interval": "1s",
"number_of_shards": "1",
"provided_name": "candidature",
"creation_date": "1694872335334",
"store": {
"type": "fs"
},
"number_of_replicas": "1",
"uuid": "9cRKu-TLRsGfwuKn2UTbWg",
"version": {
"created": "8080299"
}
}
}
}
}
```
---
### Author:  [@DWAIPAYAN\_SOM](https://discuss.elastic.co/u/DWAIPAYAN_SOM)
#### Post date: [September 18, 2023, 11:56am UTC](https://discuss.elastic.co/t/query-not-working-as-expected/343186/4 "2023-09-18T11:56:29Z")
Hi @carly.richmond The issue was resolved in stackoverflow. Yet here goes the document mapping. The problem was I was using a simple\_query\_string with Operator.Or instead I should have used "|" . I converted it to quer\_string and then with Opertor.Or it worked fine.
The problem that I also observed is when I give must\_not, it does not return the expected result. But when I use must with (query -- (NOT (Tableau) AND NOT (Tableau WorkBooks) ) it works fine
Mapping below
```auto
{
"candidature": {
"aliases": {},
"mappings": {
"properties": {
"_class": {
"type": "keyword",
"index": false,
"doc_values": false
},
"applications": {
"type": "nested",
"include_in_parent": true,
"properties": {
"_class": {
"type": "keyword",
"index": false,
"doc_values": false
},
"applnid": {
"type": "keyword"
},
"certtrainings": {
"type": "text"
},
"education": {
"type": "text"
},
"employers": {
"type": "text"
},
"forreqid": {
"type": "keyword"
},
"gaps": {
"type": "integer"
},
"skills": {
"type": "text"
},
"skilltags": {
"type": "text"
},
"summary": {
"type": "text"
},
"totalexperienceinyears": {
"type": "integer"
},
"uploadedbyuser": {
"type": "keyword"
},
"workexp": {
"type": "text"
}
}
},
"candidateId": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"contactnumber": {
"type": "keyword"
},
"createdby": {
"type": "keyword"
},
"email": {
"type": "keyword"
},
"fororg": {
"type": "keyword"
},
"name": {
"type": "text"
}
}
},
"settings": {
"index": {
"routing": {
"allocation": {
"include": {
"_tier_preference": "data_content"
}
}
},
"refresh_interval": "1s",
"number_of_shards": "1",
"provided_name": "candidature",
"creation_date": "1694872335334",
"store": {
"type": "fs"
},
"number_of_replicas": "1",
"uuid": "9cRKu-TLRsGfwuKn2UTbWg",
"version": {
"created": "8080299"
}
}
}
}
}
```
Document I was expecting
```auto
{
createdby : abc@soething.com,
applications : [
{
applnid : yy,
summary : top and Tableau Server.· Involved in dashboard test cases creation and execution, prepared the understanding and function/ process flow documents on various dashboards for the end users.· Created technical specifications document as well as functional documents in support of the user requirements.· Generate Tableau reports to analyze data from multiple data sources like Oracle, SQL Server, Excel, Flat Files, etc · Experienced in designing customized interactive dashboards in Tableau using Marks, Action, filters, parameter and calculations · Having experience in Tableau Desktop Creating Sets, Group, Sort, Parameter, Quick filters, Context Filters, Data blending, Joins and Calculations etc.Experience Details · Educational Detai,
workexp : Procurement of billing the project and Project lead · The solution involves in creating dashboards and stories that depict different levels and stages.· The very first is a managerial dashboard to give quick overview of the Categories and their overview among different geographical areas, trends and comparisons using Map Charts, Pies, Stacked Bars, Scatter Plots etc.· The second one concentrates more on slicing and dicing the inventory and sales data using Dual Axis Charts, Various line charts, Waterfall charts etc.· The last one is a blend of Calculated Fields, Table Calculations and a bit of LODs to answer different types of the requirements of the client. Role : Tableau Developer Revenue Growth in % · Used Filters to know Department wise Sales and their Cost for Particular Periods and draft various charts using Show Me in Tableau Desktop.,
education : null,
certtrainings : null,
gaps : null,
skilltags : null,
forreqid : FH-REQ-3,
uploadedbyuser : null
}
]
}
```
---
### Author:  [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)
#### Post date: [September 18, 2023, 12:07pm UTC](https://discuss.elastic.co/t/query-not-working-as-expected/343186/5 "2023-09-18T12:07:48Z")
Hi @DWAIPAYAN_SOM,
> [@DWAIPAYAN\_SOM](#):
>
> The issue was resolved in stackoverflow
Thanks for confirming. Glad you got your issue sorted. I see the answer is in this thread:
>
Posting just in case others have the same question. Thanks for sharing!
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [October 16, 2023, 12:08pm UTC](https://discuss.elastic.co/t/query-not-working-as-expected/343186/6 "2023-10-16T12:08:20Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.