我正在一个名为nested\u nicknames的嵌套文档上运行一个查询（它也嵌套在Map中）
模式基本上是

{
 id:int
 nested_nicknames: [{
                      value:string,
                      isfavourite:bool
                   }]
}

我正在尝试运行的opendistro查询

POST _opendistro/_sql
{
  "query": """SELECT i.id FROM test_nicknames_index as i WHERE EXISTS 
  (SELECT 1
    FROM i.nested_nicknames  AS q
    WHERE q.value="sql" AND q.isfavourite=true LIMIT 1)
    AND EXISTS 
  (SELECT 1
    FROM i.nested_nicknames  AS q
    WHERE q.value="sqldistro" AND q.isfavourite=false LIMIT 1) 
  LIMIT 10;"""
}

但是运行它会给我这个错误

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Unsupported subquery. Only one EXISTS or IN is supported",
    "type": "SQLFeatureNotSupportedException"
  },
  "status": 400
}

但是，如果我创建一个弹性查询，它的效果会更好！我想知道是否有什么办法可以让上面的sql查询生成下面的弹性查询

POST /test_nicknames_index/_search
{
  "_source": "ID",
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "nested_nicknames",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "nested_nicknames.value": "sql"
                    }
                  },
                  {
                    "match": {
                      "nested_nicknames.isfavourite": true
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "nested_nicknames",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "nested_nicknames.value": "sqldistro"
                    }
                  },
                  {
                    "match": {
                      "nested_nicknames.isfavourite": false
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}