Elastic search sublist query with and logic

Question

I have post below json data:

[
    {
        "id": 1,
        "shopname": "seven up",
        "shopkeeper": "John",
        "salesbooks": [
            {
                "bookid": 11,
                "bookname": "Tom-story",
                "soldout": false
            },
            {
                "bookid": 12,
                "bookname": "Iron-Man",
                "soldout": true
            }
        ]
    }
]

and I make a simple elastic query as below:

{
    "from": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "wildcard": {
                        "salesbooks.bookname": {
                            "value": "*Iron-Man*"
                        }
                    }
                },
                {
                    "term": {
                        "salesbooks.soldout": {
                            "value": false
                        }
                    }
                }
            ]
        }
    }
}

It should be empty as I want filter salesbooks.bookname contain ("iron-man") and soldout is false, it didn't work can I know what's wrong inside

Thank you

user_18778181 · Answer

The "must" clause requires that all clauses be true, in your case there is no "salesbooks.bookname": "Iron-Man" and soldout false, only "salesbooks.bookname": "Iron-Man" and soldout true.

Another important point is that the wildcard is a Term Level Query.

My example I tested (I figured salesbooks is a nested object).

PUT /house
{
  "mappings": {
    "properties": {
      "salesbooks": {
        "type": "nested",
        "properties": {
          "bookname": {
            "type": "keyword"
          },
          "soldout": {
            "type": "boolean"
          }
        }
      }
    }
  }
}

PUT /house/_doc/1
{
  "shopname": "seven up",
  "shopkeeper": "John",
  "salesbooks": [
    {
      "bookid": 11,
      "bookname": "Tom-story",
      "soldout": false
    },
    {
      "bookid": 12,
      "bookname": "Iron-Man",
      "soldout": true
    }
  ]
}

GET house/_search
{
  "from": 0,
  "query": {
    "nested": {
      "path": "salesbooks",
      "query": {
        "bool": {
          "must": [
            {
              "wildcard": {
                "salesbooks.bookname": "*Iron-Man*"
              }
            },
            {
              "term": {
                "salesbooks.soldout": {
                  "value": true
                }
              }
            }
          ]
        }
      }
    }
  }
}

Elastic search sublist query with and logic

1 Answers