Elastic-search advanced search query issue

Elastic Stack Elasticsearch
1

Dear All.

I have some use case:-

01:- i have index(index01) that contains 8 fields
:- fields like- a,b,c,d,e,f,g,h,i
:- each fields has diff value
02:- now search condition here is?
:- if i search for fields [a:"alpha"] then it will search all data matching
with field[a]
:- if search for fields [b:'data',c:'meta',d:'sata'] then it will search only
fields[b,c,d]
values but not other matched fields within [a,e,f,g,h]
03:- this is final case in, index fields[i] that contains integer value and we have to perform all above condition based on matching fields [i:1411]

thanks

2

What did you try so far?

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

3

Hi @dadoonet.

Thanks

i am trying to search the records based on below sql query condition :-1:
condition :- 01
i have 8-fields in current index use case and i want to search all fields value based on provided value with fields like-
field1:'data'
field2:'sata'
field5:2424
field8:PHM

Condition02:-
in above search field i am not providing field value for field3,4,6,7
but we need this field for next search in current query and want exact matching result with previous -
field1:'data'
field2:'sata'
field5:2424
field8:'PHM'

Condition3:-1:
when i used --and-- (MATCH IN ES)condition for
field1:'data'
field2:'sata'
field5:2424
field8:PHM

--then it fetch exact matching records but if we are not passing the value for any field this query is going to fail.

--if i used --OR--(should in ES) condition then it fetching all matching records with all fields that is wrong in my use case.

{
"query": "select * from dbdataindex_join_allcolumns_qa_modify_cm_28_08_2019 where  regionname='MSP 05S' and caremanager='HM CM' and patientname='Aaron' "

}

SAME QUERY in DSL:-
<{ "size" : 1000, "query" : { "bool" : { "must" : [ { "bool" : { "must" : [ { "term" : { "regionname.keyword" : { "value" : "MS5S", "boost" : 1.0 } } }, { "term" : { "caremanager.keyword" : { "value" : "HECM", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, { "term" : { "patientname.keyword" : { "value" : "Aaa", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : [ "@version", "active", "beneficiary_status", "care_program_name", "caremanager", "city", "dob1", "email", "medicare", "patientname", "pcp", "phone_home", "provider_type", "regionname", "sex" ], "excludes" : [ ] }, "docvalue_fields" : [ { "field" : "@timestamp", "format" : "epoch_millis" }, { "field" : "age" }, { "field" : "careprog_id" }, { "field" : "dob", "format" : "epoch_millis" }, { "field" : "log_date", "format" : "epoch_millis" }, { "field" : "patient_provider_hrk" }, { "field" : "pid" }, { "field" : "user_id" } ], "sort" : [ { "_doc" : { "order" : "asc" } } ] } />

Final Request :-

i want --all field-- in search search query but user can provide some time 3,4,5 fields only but result should come based on provided fields values.

Very thanks .
HadoopHelp

4

Hi @dadoonet.

please help :pray:

5

As discussed in the other thread you will need to construct the bool clauses based on what fields the user actually submits.

6

Hi @Christian_Dahlqvist

you are correct but i have an application that contains 8-search fields

condition here is user some time searching only 3,4,5 fields from current index

when i applied any --bool condition like ---should /must this ---is is not going to fix(as previous DSL ).

so please guide me...how can i implement all 8-fields exact match as user pass the values.

i know ,i am going something alter but this bool is not resolving this use case.

please understand my requirement i want all fields in search query but user may pass only 3,4 fields .

Thanks

7

Hi @Christian_Dahlqvist

Please look at my issue and try to help.

8

You need to create separate queries for each combination of fields.

If this is not the answer I do tho think I understand your question.

9

Hi @Christian_Dahlqvist .

but how can we implement that as seperate query for each search fields .

Please give some clue ,you are getting my issue in correct way.

Thanks
HadoopHelp

10

You need to build logic into your application that generates the query based on the users input.

11

Hi @Christian_Dahlqvist.

Thanks

is there any possible way of design query based on user input please share some link or some idea.

as per current request we have to write 8-different query based on user inputs based on condition but how.

Thanks
HadoopHelp

12

Hi @Christian_Dahlqvist and @dadoonet .

Please help me for this trouble/issue .still i am waiting for some response.

How can we write elastic query based on user input?

Thanks
HadoopHelp

13

You need to provide a script that helps playing with your data as I asked in Elastic-search advanced search query issue.

Then explain what is the exact output you'd like to see.
From that we can have a chance to understand what you want to do and if it's feasible or not.

14

Hi @dadoonet.

as we discussed previous .

i am using sample query/script below:
<{ "size" : 1000, "query" : { "bool" : { "must" : [ { "bool" : { "must" : [ { "bool" : { "must" : [ { "term" : { "patientname.keyword" : { "value" : "Aadra", "boost" : 1.0 } } }, { "term" : { "sex.keyword" : { "value" : "F", "boost" : 1.0 } } } ] } }, { "term" : { "dob1.keyword" : { "value" : "04/7/1942", "boost" : 1.0 } } } ] } }, { "term" : { "caremanager.keyword" : { "value" : "HEM", "boost" : 1.0 } } } ] } } />

but problem i am facing here :
i have to pass each doc:values if i will not pass value in this query this is fetching null value
but i want to fetch those doc:value what i passed only
this index contains 8-fields/doc and query should be capable to search all 8-fields
but may be some time user can pass only 3,4 doc:value and based on passed value we want exact data.

crying like any thing please help

Very Thanks
HadoopHelp

15

I don't understand. And I cannot reproduce anything.

16

Hi @Christian_Dahlqvist and @dadoonet.

More details :

here is index data contains all(fields/doc):

{"_index":"dbdataindex_join_allcolumns_qa_modify_cm_28_08_2019",
"_type":"caremanager",
"_id":"dp_X3GwBthBZI3VKO",
"_score":1.0,"_source":{"dob":"1992-04-05T17:30:00.000Z",
"patientname":"Aan ra",
"city":"TEst",
"provider_type":"P",
"medicare":"2293A",
"care_program_name":"Subsequent",
"@timestamp":"2019-08-29T10:00:47.594Z",
"dob1":"04/06/1942",
"log_date":"2017-22T18:30:00.000Z",
"email":"prah.kumar@hem",
"regionname":"MSS",
"phone_home":"3-3333",
"age":77,
"pcp":" ",
"active":"A",
"sex":"F",
"user_id":89816,
"caremanager":"HECCM",
"pid":null,
"@version":"1",
"patient_provider_hrk":6541,
"careprog_id":31,
"beneficiary_status":"Active"

but i want to search only below based fields/doc:values and result should be exact match:

"patientname":"Aan ra",
"regionname":"MSS",
"active":"A",
"caremanager":"HECCM",
dob1":"04/06/1942"

Note : i passed any above value then my query should fetch exact matched data only no other data like or and like operator.

but my query should be able to search only provided doc:value data.

Please continue ...

Thanks
HadoopHelp

17

Hi @dadoonet.

i think you are taking more complex . only problem is that when i use --bool-must-- in my query for search all fields/doc in current .its force to pass fields/doc:'value'.

if i don;t pass the value with search fields/doc:'' " it give nothing/null.

@Christian_Dahlqvist i think you got my problem already please suggest some idea.

Thanks
HadoopHelp

18

Hi @Christian_Dahlqvist and @dadoonet.

this is my final request from forum Team.
please look at this pic and each search fields:

image
image1052×266 15.8 KB

i given GUI view to all forum please try to understand this issue.

case 01 :

i provide patient Name ='ramaya' and gender='M' then fetch all matched ramaya as patientname and M as gender.

case 02:

if i provide patienName ='ramaya' and gender='M' and dob='15/02/1933' then fetch all matched data with given value

case 03:

all search fields will be there in query as shown in GUI. for searching purpose but user can pass only : ----patientname ,gender or dob. not all fields.

case 04:

result should be come only exact matched what user has given on GUI.

Thanks
HadoopHelp

19

As I said before you probably need to write a piece of code that creates the query dynamically based on the fields the user have specified.

If I have understood correctly a template based approach will not work, which seems to be what you are looking for.

20

Hi @Christian_Dahlqvist.

as you are saying how can this possible in current use case.

for example i have 10- search fields in GUI so we have to write 10*10 means 100-condition here and based on that condition we have to run/execute 100-elastic search query.

correct me if i am wrong here ?

but one more issue how can we handle here multi condition request here with elastic-search.

Thanks
HadoopHelp