Search for documents with exactly different fields values

I'm adding documents with the following strutucte

{
    "proposta": {
        "matriculaIndicacao": 654321,
        "filial": 100,
        "cpf": "12345678901",
        "idStatus": "3",
        "status": "Reprovada",
        "dadosPessoais": {
            "nome": "John Five",
            "dataNascimento": "1980-12-01",
            "email": "fulanodasilva@fulano.com.br",
            "emailValidado": true,
            "telefoneCelular": "11 99876-9999",
            "telefoneCelularValidado": true,
            "telefoneResidencial": "11 2211-1122",
            "idGenero": "1",
            "genero": "M"
        }
    }
}

I'm trying to perform a search with multiple field values.
I can successfull search for a document with a specific cpf atribute with the following search
{
"query": {
"term" : {
"proposta.cpf" : "23798770823"
}
}
}

But now I need to add an AND clause, like

{
  "query": {
    "term" : { 
    	"proposta.cpf" : "23798770823"
    	,"proposta.dadosPessoais.dataNascimento": "1980-12-01"
    }
  }
}

but it's returning an error message.
P.S: If possible I would like to perform a search where if the field doesn't exist, it returns the document that matches only the proposta.cpf field.

I really appreciate any help.

Take a look at the bool query. That is the query that you can use to combine multiple search criteria.

To implement your AND query, you would use the must clause of the bool query. Put the queries that must match for a document to be considered a hit in this must clause:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "proposta.cpf": "23798770823"
          }
        },
        {
          "term": {
            "proposta.dadosPessoais.dataNascimento": "1980-12-01"
          }
        }
      ]
    }
  }
}

Your second requirement, where a field the proposta.dadosPessoais.dataNascimento field can be either missing or it has to match a specific value, can be implemented by combining a must clause with a should clause. The should clause contains the "nice to have" criteria. You can put the query for proposta.dadosPessoais.dataNascimento in there, along with a bool query that uses a must_not to search for documents that are missing the value using the exists query. Next, you tell Elasticsearch that either one of these criteria has to match using "minimum_should_match": 1:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "proposta.cpf": "23798770823"
          }
        }
      ],
      "should": [
        {
          "term": {
            "proposta.dadosPessoais.dataNascimento": "1980-12-01"
          }
        },
        {
          "bool": {
            "must_not": {
              "exists": {
                "field": "proposta.dadosPessoais.dataNascimento"
              }
            }
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}

This all may look a bit complicated at first, as the boolean logic of the bool query can take some getting used to. But if you read the docs and play around with it, you'll soon understand its power.

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