# \[Q\]: Is there a 'LIKE' function in ES|QL? **URL:** \ **Category:** Elastic Search\ **Tags:** esql\ **Created:** [December 13, 2024, 5:18am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942 "2024-12-13T05:18:38Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![cjessing](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cjessing/32/133870_2.png) [@cjessing](https://discuss.elastic.co/u/cjessing)\ **Post date:** [December 13, 2024, 5:18am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/1 "2024-12-13T05:18:38Z")
So I have som data in filebeat where the following query will return some data: ```auto FROM filebeat-* | WHERE event.dataset == "cert-info.log" | KEEP tags | LIMIT 2000 ``` this results in something like this: ```auto Certificate [Private key, issuer_missing, subject_missing] [Private key, issuer_missing] ``` What I would like to do is only get the rows where `tags` contain the phrase `Private key`. I have tried adding the following `WHERE` clauses one by one but even though I dont get any errors they all result in no hits at all: ```auto WHERE tags LIKE "*Private key*" WHERE tags LIKE "%Private key%" EVAL t = LOCATE(tags, "Private key") | WHERE t > 0 ``` Is it possible to succeed in what I'm trying to do or does ES|QL simply not support such a relatively basic feature? Thanks ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [December 13, 2024, 6:28am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/2 "2024-12-13T06:28:38Z")
> [@cjessing](#): > > What I would like to do is only get the rows where `tags` contain the phrase `Private key`. > [@cjessing](#): > > Is it possible to succeed in what I'm trying to do or does ES|QL simply not support such a relatively basic feature? I do not know whether ES|QL supports this or not, so will need to let someone else respond to that. If you were to translate this type of query into Elasticsearch query clauses, which is what I believe ES/QL does behind the scenes, it would result in a wildcard query with leading and trailing wildcards. This is as far as I know by far the most inefficient query you can run in Elasticsearch and it performs and scales very badly with increasing data volumes. It is generally recommended to avoid this at all cost, at least if it has to be run against a field mapped as `keyword` and not [wildcard](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html#wildcard-field-type) If this type of query is not supported by ES|QL I would expect this to be the reason for it. ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [December 13, 2024, 6:46am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/3 "2024-12-13T06:46:59Z")
@cjessing Perhaps review the docs [LIKE](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-like-operator) [RLIKE](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-rlike-operator) > [@Christian\_Dahlqvist](#): > > which is what I believe ES/QL does behind the scenes, i Actually it is mostly a new execution framework and specifically does not translate to DSL.:). But the actual issue is `tags` is an array so it needs to be expanded first You can use [`MV_EXPAND`](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-commands.html#esql-mv_expand) ```auto FROM logs-* | MV_EXPAND tags | where tags == "Private key" | LIMIT 10 ``` ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [December 13, 2024, 6:58am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/4 "2024-12-13T06:58:50Z")
> [@stephenb](#): > > Actually it is mostly a new execution framework and specifically does not translate to DSL.:). Even if it does not directly translate to DSL I do not see how it could execute this type of logic in a much more efficient way against Lucene. If that was possible, would the wildcard query clause not have been improved as well? Has there been a major breakthrough in efficiency? ---
**Author:** ![cjessing](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cjessing/32/133870_2.png) [@cjessing](https://discuss.elastic.co/u/cjessing)\ **Post date:** [December 13, 2024, 7:01am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/5 "2024-12-13T07:01:34Z")
MV\_EXPAND!!! That was it! You're a life saver. Thanks a million! ---
**Author:** ![cjessing](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cjessing/32/133870_2.png) [@cjessing](https://discuss.elastic.co/u/cjessing)\ **Post date:** [December 13, 2024, 8:19am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/6 "2024-12-13T08:19:31Z")
Actually... to elaborate a bit... what if I want ONE hit if two conditions are met? I would like 3 seperate counts for ```auto tags == "Private key" (and nothing else) tags == [Private key, issuer_missing] (no more, no less) tags == [Private key, issuer_missing, subject_missing] (no more, no less) ``` I realize I have to do seperate queries but thats okay. ---
**Author:** ![cjessing](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cjessing/32/133870_2.png) [@cjessing](https://discuss.elastic.co/u/cjessing)\ **Post date:** [December 13, 2024, 8:47am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/7 "2024-12-13T08:47:08Z")
I think the following query will be a good place to start... ```auto FROM filebeat-* | EVAL t = MV_CONCAT(tags, ", ") | WHERE event.dataset == "cert-info.log" | WHERE (LOCATE(t, "Private key") > 0 ) | KEEP t | LIMIT 100 ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 10, 2025, 8:47am UTC](https://discuss.elastic.co/t/q-is-there-a-like-function-in-es-ql/371942/8 "2025-01-10T08:47:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.