多字段查询中的elasticsearch等价sql

iq0todco 于 2021-06-10 发布在 ElasticSearch

关注(0)|答案(1)|浏览(353)

我在下面的查询中寻找与elasticsearch等效的sql

SELECT * from table
WHERE (section , class) in (('a','1'), ('b', '2'))

我知道如何在elasticsearch中查询单个字段

SELECT * FROM table WHERE class IN ('1', '2');

elasticsearch查询-

{
  "query" : {
    "bool" : {
      "filter" : {
        "terms" : {
          "class" : ['1', '2']
        }
      }
    }
  }
}

我的实际问题陈述-
样本索引数据：

[
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "41",
    "_score" : 1.0,
    "_source" : {
      "class" : "1",
      "section" : "a",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "42",
    "_score" : 1.0,
    "_source" : {
      "class" : "2",
      "section" : "a",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "43",
    "_score" : 1.0,
    "_source" : {
      "class" : "1",
      "section" : "b",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "44",
    "_score" : 1.0,
    "_source" : {
      "class" : "2",
      "section" : "b",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "45",
    "_score" : 1.0,
    "_source" : {
      "class" : "3",
      "section" : "b",
      "attribute_3" : "hello world"
}
]

我想在（类是1，节是a）或（类是2，节是b）的数据上使用一个过滤器注意：我正在动态地准备这个'or'组合，它将是两个以上的组合。
我期望的搜索结果应该是-

[{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "41",
    "_score" : 1.0,
    "_source" : {
      "class" : "1",
      "section" : "a",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "44",
    "_score" : 1.0,
    "_source" : {
      "class" : "2",
      "section" : "b",
      "attribute_3" : "hello world"
}]

elasticsearch

来源：https://stackoverflow.com/questions/64906999/elasticsearch-equivalent-sql-in-query-with-multiple-fields

1条答案

按热度按时间

9jyewag01#

这将转化为：

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "a": 0
                }
              },
              {
                "term": {
                  "b": 9
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "a": 0
                }
              },
              {
                "term": {
                  "b": 4
                }
              }
            ]
          }
        }
      ]
    }
  }
}

但如果 a 总是 0 如您在示例中所述，查询可以重新表述为：

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "a": 0
          }
        },
        {
          "terms": {
            "b": [
              9,
              4
            ]
          }
        }
      ]
    }
  }
}

赞(0）回复(0）举报 2021-06-10

我来回答

多字段查询中的elasticsearch等价sql

1条答案

相关问题

热门标签

最新问答