Search query does not return expected records

Elastic Stack Elasticsearch
1

Hello,

i have documents with following structure:

...
// doc 1
"_source": {
		...,
		"data": {
			"main": {
                ...
				"refAccount1": 1,
				"refAccount2": 1,
				"refAccount3": 1
				"refType": 1,
			},
			"detail": {
                ...
				"feld1": "Firstname",
				"feld2": "Surname",
				"feld3": "Avatername",
				"feld4": "test",
				...
			}
		}
	}
// doc 2
   ...
   "_source": {
		...,
		"data": {
			"main": {
                ...
				"refAccount1": 2,
				"refAccount2": 3,
				"refAccount3": 3
				"refType": 1,
			},
			"detail": {
                ...
				"feld1": "Firstname",
				"feld2": "Surname",
				"feld3": "Avatername",
				"feld4": "test",
				...
			}
		}
	}
// doc 3
   ...
   "_source": {
		...,
		"data": {
			"main": {
                ...
				"refAccount1": 4,
				"refAccount2": 3,
				"refAccount3": 3
				"refType": 1,
			},
			"detail": {
                ...
				"feld1": "no",
				"feld2": "Surname",
				"feld3": "false",
				"feld4": "test",
				...
			}
		}
	}

All detail-fields defined as wildcards.

With the term *name* i try to return all records, where detail-wildcard match and with following conditions using terms-query:

refType must match the a given array value
AND at least one of the Account-Fields has to match the term o a given array value

like in in sql (fantasy code):

SELECT * FROM table
WHERE 
(
   data.detail.feld1 LIKE name
   OR 
   data.detail.feld2 LIKE name
   OR
   data.detail.feld3 LIKE name
) AND  
(
   data.main.refAccount1 IN (1,2)
   OR
   data.main.refAccount2 IN (1,2)
   OR
   data.main.refAccount3 IN (1,2)
) AND
(
   data.main.refType IN (1,2,3,4,5,6)
)

Expected: doc1, doc2

Trying with combined should & filter does not return the expected result:

{
  "query": {
    "bool": {
      "should": [
        {
          "wildcard": {
            "data.detail.feld1": {
              "value": "*name*",
              "case_insensitive": true
            }
          }
        },
        {
          "wildcard": {
            "data.detail.field2": {
              "value": "*name*",
              "case_insensitive": true
            }
          }
        },
        {
          "wildcard": {
            "data.detail.field3": {
              "value": "*name*",
              "case_insensitive": true
            }
          }
        },
        {
          "terms": {
            "data.main.refAccount1": ["1", "2"]
          }
        },
        {
          "terms": {
            "data.main.refAccount2": ["1", "2"]
          }
        },
        {
          "terms": {
            "data.main.refAccount3": ["1","2"]
          }
        }
      ],
      "filter": [
        {
          "terms": {
            "data.main.refType": ["1", "2", "3", "4", "5"]
          }
        }
      ],
      "minimum_should_match": 1
    }
  },
  "from": 0,
  "size": 10
}

Result returns unexpected doc3

I undertand, this happens because of data.detail.field2 match the wildcard.

But how can i exclude doc3 because of not matching the refAccount*?

Thx for any help

2

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