ElasticSearch:使用OR和子句创建bool查询

Question

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

我有一个SQL查询，以便

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

这是我的等效弹性查询

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
      }
    }
  }

}

Answer 2

我漏掉了应该里的另一个布尔条款

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