Must_not in bool query?

Question

Usecase : I want to delete all the documents that are not having certain id's .

Mappings:

"bookList": {
                  "properties": {
                     "bookname": {
                        "type": "string"
                     },
                     "bookId": {
                        "type": "long"
                     },
}

The content will be like this in each document : Some have inner lists let it be DOC1 :

"bookList": [
                  {
                     "bookname": "HITLER",
                     "bookId": 3163
                   },
                   {
                       "bookname": "MARTIAN", 
                       "bookId": 1210
                   }
                   ]

And some only one list like this Let it be DOC2

"bookList": [
                  {
                     "bookname": "WASHINGTON",
                     "bookId": 3163
                   }
                   ]

And another document DOC3:

 "bookList": [
                      {
                         "bookname": "SHELDON",
                         "bookId": 3163
                       },
                       {
                           "bookname": "MARVELS", 
                           "bookId": 1219
                       }
                       ]

I am using delete_by_query plugin in ES 2.4.0 to delete the documents that are NOT having bookId : 3163 using MUST_NOT in bool query . For inner lists documents(i.e.DOC1 & DOC3) it is not deleting them because MUST_NOT find's the exact term if the term is present in either of the inner lists then it is not deleting that document.

My Query is like this :

{
    "query" : {
    "bool" : {
        "must_not" : {
            "term" : {
        "bookList.bookId" :3163 
            }
    }
    }
}
}

This query is deleting only DOC2 but not DOC1. How can i able to delete the inner list documents ?

wildcard query for the book_Id you mean? Because for book_ID's i have to use unique id's — Private

lucabelluccini lucabelluccini · Accepted Answer · 2017-06-26T11:34:28

I think you should use the filter context.

In filter context, a query clause answers the question “Does this document match this query clause?” The answer is a simple Yes or No — no scores are calculated. (From: documentation)

See documentation at Elastic.

PUT _template/stackoverflow
{
  "template": "stackoverflow",
  "settings": {
    "number_of_shards": 1
  },
  "mappings": {
    "bookList": {
      "properties": {
        "bookname": {
          "type": "text"
        },
        "bookId": {
          "type": "long"
        }
      }
    }
  }
}


POST stackoverflow/bookList/1
{
  "bookList": [
    {
      "bookname": "HITLER",
      "bookId": 3163
    },
    {
      "bookname": "MARTIAN",
      "bookId": 1210
    }
  ]
}

POST stackoverflow/bookList/2
{
  "bookList": [
    {
      "bookname": "WASHINGTON",
      "bookId": 3163
    }
  ]
}

POST stackoverflow/bookList/3
{
  "bookList": [
    {
      "bookname": "SHELDON",
      "bookId": 3163
    },
    {
      "bookname": "MARVELS",
      "bookId": 1219
    }
  ]
}

POST stackoverflow/bookList/4
{
  "bookList": [
    {
      "bookname": "SHELDON",
      "bookId": 3164
    },
    {
      "bookname": "MARVELS",
      "bookId": 1220
    }
  ]
}

Execution:

GET stackoverflow/bookList/_search
{
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "must_not": {
            "term": {
              "bookList.bookId": 3163
            }
          }
        }
      }
    }
  }
}

Result:

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "stackoverflow",
        "_type": "bookList",
        "_id": "4",
        "_score": 1,
        "_source": {
          "bookList": [
            {
              "bookname": "SHELDON",
              "bookId": 3164
            },
            {
              "bookname": "MARVELS",
              "bookId": 1220
            }
          ]
        }
      }
    ]
  }
}

Execution:

GET stackoverflow/_search
{
  "query": {
    "bool": {
      "must": {
        "term": {
          "bookList.bookId": 3163
        }
      }
    }
  }
}

Result:

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "hits": {
    "total": 3,
    "max_score": 1,
    "hits": [
      {
        "_index": "stackoverflow",
        "_type": "bookList",
        "_id": "1",
        "_score": 1,
        "_source": {
          "bookList": [
            {
              "bookname": "HITLER",
              "bookId": 3163
            },
            {
              "bookname": "MARTIAN",
              "bookId": 1210
            }
          ]
        }
      },
      {
        "_index": "stackoverflow",
        "_type": "bookList",
        "_id": "2",
        "_score": 1,
        "_source": {
          "bookList": [
            {
              "bookname": "WASHINGTON",
              "bookId": 3163
            }
          ]
        }
      },
      {
        "_index": "stackoverflow",
        "_type": "bookList",
        "_id": "3",
        "_score": 1,
        "_source": {
          "bookList": [
            {
              "bookname": "SHELDON",
              "bookId": 3163
            },
            {
              "bookname": "MARVELS",
              "bookId": 1219
            }
          ]
        }
      }
    ]
  }
}

Must_not in bool query?

1 Answers