SQL LIKE with leading wildcard not working correctly in Search API
closed
J
Jonquil yellow Owl
When sending a SQL query against the Person Search API, LIKE does not appear to translate/execute correctly in the query. Example SQL query below:
SELECT * FROM person WHERE job_company_id IN ('andela','datadog','digitalocean','mongodbinc','squarespace','sprinklr','doubleverify-inc','clear-by-alclear-llc','mypizza','zetaglobal') AND job_title LIKE '%data%'
This query runs successfully but returns a 404 with 0 profiles. When dropping the leading % from the LIKE the query appears to execute correctly and return profiles.
Similarly, running the same original query translated into ElasticSearch works correctly and returns 378 profiles. Query below:
{
"query" : {
"bool" : {
"must" : [
{
"terms" : {
"job_company_id" : ["andela", "datadog", "digitalocean", "mongodbinc", "squarespace", "sprinklr", "doubleverify-inc", "clear-by-alclear-llc", "mypizza", "zetaglobal"]}},
{
"wildcard" : {
"job_title" : {
"wildcard" : "
data
"}
}
}
]
}
}
}
H
Haley Bryan
Merged in a post:
SQL: LIKE keyword doesn't work for all string columns
Q
Quasar blue Gerbil
B
Ben Eisenberg
marked this post as
closed
B
Ben Eisenberg
marked this post as
doing now
B
Ben Eisenberg
Hey Jonquil yellow Owl were you making this SQL query in the browser // postman or in code?
J
Jonquil yellow Owl
Ben Eisenberg: In code.
B
Ben Eisenberg
Jonquil yellow Owl: I replicated this using GET, because of how URL encoding handles % signs. You can either use our newly supported POST functionality for search (https://docs.peopledatalabs.com/docs/search-api#using-post-requests) or escape the % "job_title LIKE '%25data%'".
J
Jonquil yellow Owl
Ben Eisenberg: Ah got it, will try that next time. We prefer SQL over ES in general.
B
Ben Eisenberg
Jonquil yellow Owl: Yep I agree that its generally easier to onboard with. Our plan with the next version of our endpoint is to deprecate GET requests for the search API so people don't accidentally run into URL encoding issues.