# Best way to do compound bool query with exact matching strings on analyzed fields?

**URL:** <https://discuss.elastic.co/t/best-way-to-do-compound-bool-query-with-exact-matching-strings-on-analyzed-fields/79820>\
**Category:** Elasticsearch\
**Created:** [March 24, 2017, 12:24am UTC](https://discuss.elastic.co/t/best-way-to-do-compound-bool-query-with-exact-matching-strings-on-analyzed-fields/79820 "2017-03-24T00:24:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![scotttam](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/scotttam/32/10080_2.png) [@scotttam](https://discuss.elastic.co/u/scotttam)\
**Post date:** [March 24, 2017, 12:24am UTC](https://discuss.elastic.co/t/best-way-to-do-compound-bool-query-with-exact-matching-strings-on-analyzed-fields/79820/1 "2017-03-24T00:24:29Z")

</div>

I'm sure this has come up before but I can't seem to find a definitive answer.

I effectively want to do this type of query in ES:

```
SELECT * FROM my_doc WHERE account_id = 1 AND name = "first doc"

```

Here's what my index/mappings look like:

```
curl -XPUT 'localhost:9200/my_index?pretty' -d'
{
  "settings" : {
      "number_of_shards" : 1
  },
  "mappings" : {
    "my_doc" : {
      "dynamic" : "false",
      "include_in_all" : false,
      "date_detection" : false,
      "properties" : {
        "account_id" : {
          "type" : "string",
          "index" : "not_analyzed",
          "norms" : {
            "enabled" : true
          },
          "include_in_all" : true
        },
        "name" : {
          "type" : "string",
          "fields" : {
            "cjk" : {
              "type" : "string",
              "analyzer" : "cjk"
            },
            "english" : {
              "type" : "string",
              "analyzer" : "english"
            }
          },
          "include_in_all" : true
        }
      }
    }
  }
}'

```

And some data to search against:

```
curl -XPOST 'localhost:9200/_bulk?pretty' -d'
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "1" } }
{ "account_id" : "1", "name": "first doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "2" } }
{ "account_id" : "1", "name": "second doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "3" } }
{ "account_id" : "1", "name": "third doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "4" } }
{ "account_id" : "2", "name": "first doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "5" } }
{ "account_id" : "2", "name": "second doc" }

```

This ES query gets me the answer I want. Wondering if this is the best approach for finding the exact value of a string against an analyzed field or is there a better way.

```
curl -XGET 'localhost:9200/my_index/_search?pretty' -d'
{
  "query": {
    "bool": { 
      "must": [{ "term": { "account_id": 1 } }, { "match": { "name": { "query": "\"first doc\"", "operator" : "and" } } }]
    }
  }
}'
```

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [March 24, 2017, 12:26pm UTC](https://discuss.elastic.co/t/best-way-to-do-compound-bool-query-with-exact-matching-strings-on-analyzed-fields/79820/2 "2017-03-24T12:26:07Z")

</div>

If you want exact matches on "name" (like the SQL query does) you can add another subfield to your mapping for "name" that leaves it not analyzed. In 5.x you can do this e.g. with:

```auto
"fields" : {
            ...
            "raw" : {
              "type": "keyword"
            }
          }

```

I think in earlier versions you would need `{"type": "string", "index": "not_analyzed"}`. Now you can use another `term` query on the name field:

```auto
GET /my_index/_search?pretty
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "account_id": 1
          }
        },
        {
          "term": {
            "name.raw": "first doc"
          }
        }
      ]
    }
  }
}

```

Note that if you don't need scores you can put the two `term` queries in the `filter` section.

---

<div class="post-metadata">

**Author:** ![scotttam](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/scotttam/32/10080_2.png) [@scotttam](https://discuss.elastic.co/u/scotttam)\
**Post date:** [March 24, 2017, 9:10pm UTC](https://discuss.elastic.co/t/best-way-to-do-compound-bool-query-with-exact-matching-strings-on-analyzed-fields/79820/3 "2017-03-24T21:10:20Z")

</div>

Thanks for pointing me in the right direction, Christoph! Here's how I got the example working on 5.x:

```
curl -XPUT 'localhost:9200/my_index?pretty' -d'
{
  "mappings": {
    "my_doc": {
      "properties": {
        "account_id" : {
          "type": "keyword",
          "include_in_all" : true
        },
        "name": {
          "type": "text",
          "fields": {
            "cjk" : {
              "type" : "string",
              "analyzer" : "cjk"
            },
            "raw": { 
              "type": "keyword"
            }
          },
          "include_in_all" : true
        }
      }
    }
  }
}
'

curl -XPOST 'localhost:9200/_bulk?pretty' -d'
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "1" } }
{ "account_id" : "1", "name": "first doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "2" } }
{ "account_id" : "1", "name": "second doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "3" } }
{ "account_id" : "1", "name": "third doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "4" } }
{ "account_id" : "2", "name": "first doc" }
{ "index" : { "_index" : "my_index", "_type" : "my_doc", "_id" : "5" } }
{ "account_id" : "2", "name": "second doc" }

curl -XGET 'localhost:9200/my_index/_search?pretty' -d'
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "account_id": "1"
          }
        },
        {
          "term": {
            "name.raw": "first doc"
          }
        }
      ]
    }
  }
}
'
```

---

<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:** [April 21, 2017, 9:10pm UTC](https://discuss.elastic.co/t/best-way-to-do-compound-bool-query-with-exact-matching-strings-on-analyzed-fields/79820/4 "2017-04-21T21:10:24Z")

</div>

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