Elasticsearch过滤查询构建合理性验证：SQL转ES查询咨询

我在构建Elasticsearch过滤条件时存在疑问，现有原始Elasticsearch查询语句：

{
   "query":{
      "bool":{
         "must":[
            {
               "match":{
                  "account_id":1231231231
               }
            },
            {
               "multi_match":{
                  "fuzziness":"AUTO",
                  "query":"topics",
                  "type":"most_fields",
                  "fields":[
                     "title.value^11",              
                     "parent_name^4",
                     "*"
                  ],
                  "operator":"and"
               }
            }
         ],
         "filter":[
            
         ],
         "must_not":{
            "terms":{
               "published_status.raw":[
                  "Disabled"
               ]
            }
         }
      }
   },
   "_source":[
      "node_type",
      "parent_name",
      "node_name",
      "title"
   ],
   "size":5,
   "indices_boost":{
      "tables":7,
      "columns":1
   },
   "sort":[
      "_score",
      {
         "tbl_popularity_info.NoOfTimeUsed":{
            "order":"desc",
            "unmapped_type":"long"
         }
      }
   ]
}

需要添加的筛选条件对应的SQL语句如下：

where (( dmn_id in (123,301)) or ( cat_id in (300))) and not (( coalesce(trm_status_id,'' ) in ('SUG') ) AND ( string_to_array(coalesce(tag_ids,'' ),',') && ARRAY['314'] ) )

我自己转换后的Elasticsearch过滤条件：

"filter": [
    {
      "bool": {
        "should": [
          {
            "terms": {
              "dmn_id": [123,301]
            }
          },
          {
            "terms": {
              "cat_id": [300]
            }
          }
        ]
      }
    },
    {
      "bool": {
        "must_not": [
          {
            "bool": {
              "must": [
                {
                  "terms": {
                    "trm_status": ["SUG"]
                  }
                },
                {
                  "terms": {
                    "tags": ["314"]
                  }
                }
              ]
            }
          }
        ]
      }
    }
]

整合后的最终Elasticsearch查询语句：

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "account_id": 1231231231
          }
        },
        {
          "multi_match": {
            "fuzziness": "AUTO",
            "query": "topics",
            "type": "most_fields",
            "fields": [
              "title.value^11",
              "parent_name^4",
              "*"
            ],
            "operator": "and"
          }
        }
      ],
      "filter": [
        {
          "bool": {
            "should": [
              {
                "terms": {
                  "dmn_id": [123,301]
                }
              },
              {
                "terms": {
                  "cat_id": [300]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "bool": {
                  "must": [
                    {
                      "terms": {
                        "trm_status": ["SUG"]
                      }
                    },
                    {
                      "terms": {
                       "tags": ["314"]
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ],
      "must_not": {
        "terms": {
          "published_status.raw": ["Disabled"]
        }
      }
    }
  },
  "_source": [
    "node_type",
    "node_sub_type"
  ],
  "size": 5,
  "indices_boost": {
    "tables": 7,
    "datasets": 5,
    "terms": 3,
    "columns": 1
  },
  "sort": [
    "_score",
    {
      "tbl_popularity_info.NoOfTimeUsed": {
        "order": "desc",
        "unmapped_type": "long"
      }
    }
  ]
}

请问该查询的逻辑是否正确？

验证结果 #

整体逻辑是正确的，但有几个细节需要注意：

• 第一个筛选条件（dmn_id in (123,301) or cat_id in (300)）：使用bool.should实现OR逻辑是正确的，由于该条件放在filter数组中，外层bool会自动要求至少匹配一个should子句，完全符合SQL的OR逻辑。
• 第二个筛选条件（排除trm_status_id='SUG'且tag_ids包含314的记录）：用bool.must_not包裹同时满足两个条件的bool.must结构，完美对应SQL中的not (A AND B)逻辑，是正确的。
• 字段映射验证：需要确保trm_status字段与SQL中的trm_status_id对应；tags字段如果是keyword类型的数组，当前terms查询有效，若为逗号分隔的字符串，需确保字段分词逻辑支持匹配，否则需要调整查询方式。
• 语法小问题：最终查询的_source数组末尾有多余逗号，需删除以避免JSON语法错误。

内容的提问来源于stack exchange，提问作者Ninja