CODEWITHSUNDEEP
elasticsearch
Lorenzo S
Lorenzo S

Reputation: 1397

ElasticSearch: (OR) wildcard on two fields AND match exactly one

I am very new to the Elastic world and I am having a lot of troubles understand the logic behind some operations. Currently studying and trying a lot of stuff, so I am a bit confused right now and I need some hints.

The scenario. I have the following (client type) documents in the clients index (don't worry: the names are football players and the phone numbers are not real):

    {
      "firstName" : "Alessandro",
      "lastName" : "Nesta",
      "phoneNumber" : "+3949304903",
      "merchantId" : "90339203812831293"
    },
    {
      "firstName" : "Alessandro",
      "lastName" : "Del Piero",
      "phoneNumber" : "+39129302902",
      "merchantId" : "90339203812831293"
    },
    {
      "firstName" : "Alessandro",
      "lastName" : "Costacurta",
      "phoneNumber" : "+39098921321",
      "merchantId" : "1239021392839238"
    }

Now, the search query I am looking for basically does this: given a merchantId and a query string, try to find the query string inside firstName or inside lastName, but be sure that merchantId is exactly like the one passed in the query.

Let's say I have a query string "Aless" and a merchantId "90339203812831293 ", in the SQL world I was going to do:

SELECT
*
FROM
  `clients`
WHERE
  `merchantId` = "90339203812831293"
AND 
(
  `firstName` LIKE "Aless%" OR `lastName` LIKE "Aless%"
);

And the results should be:

{
  "firstName" : "Alessandro",
  "lastName" : "Nesta",
  "phoneNumber" : "+3949304903",
  "merchantId" : "90339203812831293"
},
{
  "firstName" : "Alessandro",
  "lastName" : "Del Piero",
  "phoneNumber" : "+39129302902",
  "merchantId" : "90339203812831293"
}

What I have tried? Basically different from combinations, from putting the merchantId in the filter section, to multi_match.

{
"query": {
  "bool": {
    "should": [
      {
        "wildcard": {
          "firstName": "aless*"
        }
      },
      {
        "wildcard": {
          "lastName": "aless*"
        }
      }
    ],
    "must": {
      "match": {
        "merchantId": "90339203812831293"
      }
    }
  }
}

0 results (If I remove the merchantId part works fine).

I have tried with query strings, too:

{
"query": {
  "bool": {
    "must": {
      "query_string": {
        "query": "Del*",
        "fields": [
          "firstName",
          "lastName"
        ]
      }
    },
    "filter": {
      "term": {
        "merchantId": "90339203812831293"
      }
    }
  }
}
}

Same problem. Can you please me point to what I am doing wrong? Thanks in advance!

Upvotes: 0

Views: 2269

Answers (2)

Anita
Anita

Reputation: 19

Did you try the multi match way?

{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "Aless",
            "fields": ["firstName", "lastName"]
         }
        },
        {
          "match": {
            "merchant_id": "90339203812831293"
          }
        }
      ]
    }
  }
}

or

{
  "query": {
    "bool": {
      "must": 
            {
              "multi_match": {
                "query": "Aless",
                "fields": ["firstname", "lastname"]
          }
        },
        "filter": {
          "term": {
            "merchant_id": "90339203812831293"
          }
        }
    }
  }
}

Upvotes: 0

Lorenzo S
Lorenzo S

Reputation: 1397

That's incredible, 1 day of full frustration trying and as soon as I have asked here I finally found a solution:

{
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "Aless*",
            "fields": [
              "firstName",
              "lastName"
            ]
          }
        },
        {
          "match": {
            "merchant_id": "90339203812831293"
          }
        }
      ]
    }
  }
}

However I am more than open to new solutions because I am pretty sure this is not the best one. I can still modify even the structure of the document, if needed.

Thanks!

Upvotes: 1

Related Questions