# How best to craft this query?

**URL:** <https://discuss.elastic.co/t/how-best-to-craft-this-query/34151>\
**Category:** Elasticsearch\
**Created:** [November 9, 2015, 1:24pm UTC](https://discuss.elastic.co/t/how-best-to-craft-this-query/34151 "2015-11-09T13:24:11Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Greg\_Mowery](https://avatars.discourse-cdn.com/v4/letter/g/b9bd4f/32.png) [@Greg\_Mowery](https://discuss.elastic.co/u/Greg_Mowery)\
**Post date:** [November 9, 2015, 1:24pm UTC](https://discuss.elastic.co/t/how-best-to-craft-this-query/34151/1 "2015-11-09T13:24:12Z")

</div>

I am working on switching a legacy search app to use Elastic. Having cataloged the data, I am now looking at some of their typical queries, and writing a parsing routine to switch these to elastic. I am stumped on how to convert the below query into the equivalent elastic syntax. If anyone can offer any suggestions, it would help a lot.

TITLE,KEYWORDS,KEYWORDS+  
("RNA"  
"RNAS"  
"MTRNA"  
"TRNA\*"  
"RRNA\*"  
"RIBOSOM\*"  
"RIBOZYME\*") AND (("INTERFER\*"  
"GENE SILENC\*"  
"INTERACTION\*"  
"PIWI"  
"SPLICING"  
"SPLICE\*"  
"CODING"  
"NONCODING"  
"ENCOD\*"  
"HELICASE\*"  
"PROCESSOSOME\*"  
"EDITING"  
"BIOGENE\*"  
"PROCESSING"  
"BINDING"  
"SUBUNIT\*"  
"HELIX"  
"STEM CELL\*"  
"EUKARYOTIC\*"  
"PROKARYOTIC\*"  
"VIRAL SYSTEM\*"  
"STRUCTURAL ANALY\*"  
"BIOCHEMICAL\*"  
"BIOPHYSICAL\*"  
"BIOGENESIS\*"  
"CHEMISTRY") NOT ("INTERFERON\*"))

This query will be issued against 3 fields (not an issue), and has nested ANDs/NOTs (not an issue). What I am stumbling over are the terms "STEM CELL\*" or "STRUCTURAL ANALY\*" (anything with a trailing wildcard). In the current system they preform as a match\_phrase\_prefix query. I am fumbling on how to mix and match this into a workable query.

How could I best output this in Elastic?

Thanks  
Greg

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [November 9, 2015, 4:43pm UTC](https://discuss.elastic.co/t/how-best-to-craft-this-query/34151/2 "2015-11-09T16:43:24Z")

</div>

> [@Greg\_Mowery](#):
>
> ("RNA" "RNAS" "MTRNA" "TRNA\*" "RRNA\*" "RIBOSOM\*" "RIBOZYME\*")

Can I assume these are all optional OR'd keywords, regardless of whether they are wildcard or not?? E.g. `"RNA" OR "RNAS" OR "TRNA*"`, etc?

If that's true, I think the easiest option is for your app to separate "single words" from "wildcards". All single words going into one large `match`, all wildcards go into individual `match_phrase_prefix`. Something like this:

```json
{
   "query": {
      "bool": {
         "must": [
            {
               "bool": {
                  "should": [
                     {
                        "match": {
                           "keyword": "RNAS MTRNA"
                        }
                     },
                     {
                        "match_phrase_prefix": {
                           "keyword": "TRNA"
                        }
                     },
                     {
                        "match_phrase_prefix": {
                           "keyword": "RRNA"
                        }
                     },
                     ...
                  ]
               }
            },
            {
               "bool": {
                  "should": [
                     {
                        "match": {
                           "keyword": "PIWI SPLICING CODING NONCODING EDITING PROCESSING BINDING CHEMISTRY"
                        }
                     },
                     {
                        "match_phrase_prefix": {
                           "keyword": "INTERFER"
                        }
                     },
                     {
                        "match_phrase_prefix": {
                           "keyword": "GENE SILENC"
                        }
                     },
                     ...
                  ],
                  "must_not": [
                     {
                        "match_phrase_prefix": {
                           "keyword": "INTERFERON"
                        }
                     }
                  ]
               }
            }
         ]
      }
   }
}

```

So we have two levels of `bools` to setup the AND/OR/NOT sequence that you have, then a set of `should` clauses in the interior which has a single match for all single words (and defaults to OR'ing them) and then individual phrase prefix for all the wildcards.

That should be a fairly close approximation of your query, if I understand correctly. That said...I'm wary of any query that needs so many wildcards, for performance reasons. So you may want to investigate alternate schemes to reduce your wildcards if you run into performance problems =)

---

<div class="post-metadata">

**Author:** ![Greg\_Mowery](https://avatars.discourse-cdn.com/v4/letter/g/b9bd4f/32.png) [@Greg\_Mowery](https://discuss.elastic.co/u/Greg_Mowery)\
**Post date:** [November 9, 2015, 7:04pm UTC](https://discuss.elastic.co/t/how-best-to-craft-this-query/34151/3 "2015-11-09T19:04:40Z")

</div>

Your assumption is correct, they all are OR'd. Now that I can see the structure of how the query is constructed, its making some sense.

I really appreciate the help!

Greg

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [November 9, 2015, 8:01pm UTC](https://discuss.elastic.co/t/how-best-to-craft-this-query/34151/4 "2015-11-09T20:01:14Z")

</div>

Np, happy to help! Lemme know how it goes...as an ex-biologist, your query has piqued my curiosity 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 11:39pm UTC](https://discuss.elastic.co/t/how-best-to-craft-this-query/34151/5 "2017-07-05T23:39:41Z")

</div>