mysql - 将复杂的mysql条件转换为elasticsearch

Question

我需要帮助将 mysql 查询条件转换为 elasticsearch 查询条件，但我陷入困境。有人可以帮我吗

问题在于这部分查询具有多个 OR，应转换为 elasticsearch should 语句

and(
    `seller`.`enabled_inventory` = '0'
    or(
        seller.`enabled_inventory` = '1'
        and `inventory_id` is null
    )
    or(
        `seller`.`enabled_inventory` = '1'
        and `unit`.`inventory_id` is not null
        and `unit`.`in_stock` = '1'
        and `unit`.`stock_quantity` > '0'
    )
)

这是我到目前为止所拥有的

mysql条件

and(
    `seller`.`enabled_inventory` = '0'
    or(
        `seller`.`enabled_inventory` = '1'
        and `inventory_id` is null
    )
    or(
        `seller`.`enabled_inventory` = '1'
        and `unit`.`inventory_id` is not null
        and `unit`.`in_stock` = '1'
        and `unit`.`stock_quantity` > '0'
    )
)
and `unit`.`value` > '0'
and `products`.`is_hidden` in('0')
and `seller`.`enabled_medical_orders` = '1'
and `seller`.`id` in(1,2,3)
and `seller`.`master_is_working` = '1'
and `product`.`category_id` in('1')

这是我到目前为止的Elasticsearch查询

{
  "query": {
    "bool": {
      "must": {
        "bool": {
          "minimum_should_match": 1,
          "should": [
            {
              "bool": {
                "must": {
                  "match": {
                    "dispensary.enabled_inventory": false
                  }
                }
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "match": {
                      "dispensary.enabled_inventory": true
                    }
                  },
                  {
                    "match": {
                      "units.inventory_id": 0
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "match": {
                      "dispensary.enabled_inventory": true
                    }
                  },
                  {
                    "match": {
                      "units.in_stock": true
                    }
                  }
                ],
                "filter": [
                  {
                    "range": {
                      "units.inventory_id": {
                        "gt": 0
                      }
                    }
                  },
                  {
                    "range": {
                      "units.stock_quantity": {
                        "gt": 0
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "filter": [
        {
          "term": {
            "dispensary.master_is_working": true
          }
        },
        {
          "term": {
            "dispensary.enabled_medical_orders": true
          }
        },
        {
          "range": {
            "units.value": {
              "gt": 0
            }
          }
        },
        {
          "term": {
            "is_hidden": 0
          }
        },
        {
          "terms": {
            "category_id": [
              1
            ]
          }
        },
        {
          "terms": {
            "dispensary.id": [
              1,
              2,
              3
            ]
          }
        }
      ]
    }
  }
}  

Answer 1

尝试不使用“过滤器”:

{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "bool": {
                  "must": {
                    "match": {
                      "dispensary.enabled_inventory": false
                    }
                  }
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "match": {
                        "dispensary.enabled_inventory": true
                      }
                    },
                    {
                      "match": {
                        "units.inventory_id": 0
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "match": {
                        "dispensary.enabled_inventory": true
                      }
                    },
                    {
                      "range": {
                        "units.inventory_id": {
                          "gt": 0
                        }
                      }
                    },
                    {
                      "match": {
                        "units.in_stock": true
                      }
                    },
                    {
                      "range": {
                        "units.stock_quantity": {
                          "gt": 0
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "range": {
            "units.value": {
              "gt": 0
            }
          }
        },
        {
          "term": {
            "is_hidden": 0
          }
        },
        {
          "term": {
            "dispensary.enabled_medical_orders": true
          }
        },
        {
          "terms": {
            "dispensary.id": [
              1,
              2,
              3
            ]
          }
        },
        {
          "term": {
            "dispensary.master_is_working": true
          }
        },
        {
          "terms": {
            "category_id": [
              1
            ]
          }
        }
      ]
    }
  }
}