sql - ElasticSearch:使用OR AND子句创建 bool 查询

Question

我正在尝试将sql查询转换为其等效的elasticsearch查询(sql tabel和ES索引相似)。它是AND / OR的组合。

我有这样一个SQL查询

SELECT DISTINCT N_ID 
FROM   MYTABLE 
WHERE  ( [C_ID] = 4 AND E_ID = 765) 
         OR ( C_ID = 6 AND E_ID = 642 ))

这是我等效的 flex 查询

GET  mytable_index/_search
{
    "query": {
        "bool": {
            "should": [{
                    "must": [{
                            "term": {
                                "C_ID": 4
                            }
                        }, {
                            "term": {
                                "E_ID": 765
                            }
                        }
                    ]
                }, {
                    "must": [{
                            "term": {
                                "C_ID": 6
                            }
                        }, {
                            "term": {
                                "E_ID": 642
                            }
                        }
                    ]
                }
            ]
        }
    }
}

但我得到以下异常:

 {
            "type": "parsing_exception",
            "reason": "[must] query malformed, no start_object after query name",
            "line": 5,
            "col": 14
  }

Answer 1

This problem will not simply  solve using query. For achiving the distinct values you  need aggregations.

Please refer to my query

{
    "query": {
        "bool": {
            "should": [{
                    "bool": {
                        "must": [{
                                "match": {
                                    "C_ID": 4
                                }
                            }, {
                                "match": {
                                    "I_ID": 765
                                }
                            }
                        ]
                    }
                }, {
                    "bool": {
                        "must": [{
                                "match": {
                                    "C_ID": 6
                                }
                            }, {
                                "match": {
                                    "I_ID": 642
                                }
                            }
                        ]
                    }
                }
            ]
        }
    },
    "size":0,
    "aggs": {
    "distinct_values": {
      "terms": {
        "field": "N_ID",
        "size": 1000
      }
    }
  }

}