Query not working as expected

Elastic Stack Elasticsearch
1

The below are my query for selection and rejection.

Selection :

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

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

2

Hi @DWAIPAYAN_SOM,

Can you share your document mapping and an example document that you expect to be returned by your query?

3

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

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

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

{
    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

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

{
    "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"
                }
            }
        }
    }
}
4

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

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

{
    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
        }
    ]
}
5

Hi @DWAIPAYAN_SOM,

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!

1 Like
6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.