Query with more than one field

[schneider (Mateus)]

Hello, i want the data witch : field "transacao" = " QuarantineTransac" and the field
"assunto" = "qualquerassunto", but is retrieving data witch one OR another no the 2 at the same time

{
    "query": {
        "bool": {
            "must": [
                {"match": {"transacao": " QuarantineTransac"}},
                {"match": {"assunto": "qualquerassunto"}}
            ],
            "filter": [
                {"range": {"data1": {"from": "now-1h", "to": "now"}}}       
                ]
        }
    }
}

Thx.

[Vinayak_Sapre]

Can you post mappings for the two fields?

[schneider (Mateus)]

Is this:

filter
{
	if "imsva" in [tags]
	{
		csv 
		{
			source => "message"
            columns => 
			[ 
				"transacao","data1","data2","data3","campo5","id","id2","campo8","remetente",
				"destinatario","assunto","host_origen","host_destino","resposta_server",
				"status","campo16","campo17","campo18","data4","data5","campo21","campo22","anexo"
            ]
			separator => "teste"
        }
		date
		{
			match => [ "data1", "yyyy MMM dd HH:mm:ss ZZ" ]
			target =>"data1"
		}
		mutate 
		{
			remove_field => [ "message" ]
		}
    }
}

[Vinayak_Sapre]

@schneider
I meant how these are defined in the index. Text vs. keyword. Analyzer etc.

GET <index_name>/_mappings?pretty

[schneider (Mateus)]

Thank you, this is the response:

{
"imsva_message" : {
"mappings" : {
"doc" : {
"properties" : {
"@timestamp" : {
"type" : "date"
},
"@version" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},"transacao" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"assunto" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"data1" : {
"type" : "date"
}
}
}
}
}
}

[schneider (Mateus)]

i font this:

{"size":0,
"aggs":{
"aggdata":{
"filter":{
"bool": {
"filter":[
{"range":{"data1":{"from": _from, "to": "now"}}},
{"match_phrase": {"assunto": {"query": assunto}}}
]
}
},
"aggs":{
"aggassunto":{
"terms":{
"field":"transacao.keyword",
"min_doc_count":min_doc_count,
"size":size
}
}
}
}
}
}
and is working for me. thx for the help @Vinayak_Sapre

[Vinayak_Sapre]

@schneider
Your two queries are completely different. The original non-working query will return documents matching 3 criteria and second matches 2 criteria and get top transacao. Rough equivalent SQLs are

SELECT * 
FROM <INDEX> 
WHERE transacao = " QuarantineTransac" and assunto = "qualquerassunto" and data1 between (now-1h, now)

SELECT  transacao, count as cnt
FROM <INDEX> 
WHERE assunto = "qualquerassunto" and data1 BETWEEN (now-1h, now) 
GROUP BY transacao 
ORDER BY cnt DESC

Can you post example documents that match but not expected to match and documents that do not match but expected to match?

Only transacao, assunto and data1 values are important. You can omit other attributes if you prefer.

[schneider (Mateus)]

I am using 2 searches:

1)=

{"size":0,
"aggs":{
"aggdata":{
"filter":{
"bool": {
"filter":[
{"range":{"data1":{"from":_from,"to":"now"}}},
{"term": {"message_direction": "incoming"}}
]
}
},
"aggs":{
"aggassunto":{
"terms":{
"field":"assunto.keyword",
"min_doc_count":min_doc_count,
"size":size
}
}
}
}
}
}

This search return the "assunto" witch more score, after this, i search again witch each of the those "assunto" using this:

{"size":0,
"aggs":{
"aggdata":{
"filter":{
"bool": {
"filter":[
{"range":{"data1":{"from": _from, "to": "now"}}},
{"match_phrase":
{"assunto":
{"query": assunto}}}
]
}
},
"aggs":{
"aggassunto":{
"terms":{
"field":"transacao.keyword",
"min_doc_count":min_doc_count,
"size":size
}
}
}
}
}
}