elasticsearch-sql-NestedTypes queries

NestedTypes queries

shiyuan edited this page on 5 Jan · 5 revisions
嵌套类型查询
shiyuan在1月5日·5版修改了此页

Nested Types
      Read about NestedTypes and what they are good for here
      From 1.4.7/2.0.2/2.1.0 version of elasticsearch-sql we have support for using nestedTypes.
      We are supporting queries and aggregations!
嵌套类型
      阅读关于NestedTypes以及它们在这里的优点
      从elasticsearch-sql的1.4.7 / 2.0.2 / 2.1.0版本我们支持使用nestedTypes。
      我们支持查询和聚合！

Query nested fields
      Simple Query (one field)
      In order to query a nested field all you need to do is add the "nested" function on the field.
      SELECT * FROM myIndex where nested(comments.message)='hello'
查询嵌套字段
      简单查询（一个字段）
      为了查询嵌套字段，你需要做的就是在字段上添加“嵌套”函数。
      SELECT * FROM myIndex where nested(comments.message)='hello'

      If you want to specify the path implicitly use:
      SELECT * FROM myIndex where nested(comments.message,'comments')='hello'
      如果您想要隐式指定路径，请使用：
      SELECT * FROM myIndex where nested(comments.message,'comments')='hello'

      Complex Query (more than one field)
      The syntax is simply nested('nested_path',where condition) for example:
      SELECT * FROM myIndex where nested('comments', comments.message = 'hello' and comments.likes > 3)
      复杂查询（多个字段）
      语法只是嵌套（'nested_path'，其中条件），例如：
      SELECT * FROM myIndex where nested('comments', comments.message = 'hello' and comments.likes > 3)

Aggregate on nested fields

       Simple term aggregation
       Wrap the string field with nested function
       SELECT count(*) as numOfComments FROM myIndex where nested(comments.age) > now-1d GROUP BY nested(comments.author)
在嵌套字段上聚合
       简单的术语聚合
        用嵌套函数包装字符串字段
       SELECT count(*) as numOfComments FROM myIndex where nested(comments.age) > now-1d GROUP BY nested(comments.author)

      Metric aggregations
      Just wrap the nested field with nested function
      SELECT sum(nested(comments.likes)) as sumOfInnerLikes FROM myIndex
      度量集合
      只需用嵌套函数包装嵌套字段
      SELECT sum(nested(comments.likes)) as sumOfInnerLikes FROM myIndex

     Buckets aggregation
      Add the 'nested' option like this: the value should be the nested path.
      select count(*) from index group by date_histogram('field'='message.date','interval'='1d','alias'='day', 'nested' ='message')
     水桶聚集
     像这样添加'嵌套'选项：值应该是嵌套路径。
     select count(*) from index group by date_histogram('field'='message.date','interval'='1d','alias'='day', 'nested' ='message')

Reverse nested aggregations
        Read about the need for reverse-nested aggregation here use it like you use nested aggregation
        Be sure you know where to jump
        examples:

        jump back to root
        SELECT sum(reverse_nested(someField)) alias FROM index GROUP BY nested(message.info)
        jump to another nested object which is inside your current nested path
        SELECT sum(reverse_nested(message.otherField,'message')) alias FROM index GROUP BY nested(message.info)
         jump to another nested object which is outside your current nested path (jumps back to root and do a nested agg)
        SELECT sum(reverse_nested(otherNested.otherField,'~otherNested')) alias FROM index GROUP BY nested(message.info)
        use it on buckets with the 'reverse_nested' on which you should add the path

       反向嵌套聚合
        阅读有关反向嵌套聚合的需求，请使用它，就像使用嵌套聚合一样
       确保你知道在哪里跳
       例子：

        跳回到根
        SELECT sum（reverse_nested（someField））别名FROM索引GROUP BY nested（message.info）
        跳转到您当前嵌套路径中的另一个嵌套对象
        SELECT sum（reverse_nested（message.otherField，'message'））别名FROM索引GROUP BY nested（message.info）
        跳到当前嵌套路径之外的另一个嵌套对象（跳回到根并执行嵌套agg）
        SELECT sum(reverse_nested(otherNested.otherField,'~otherNested')) alias FROM index GROUP BY nested(message.info)
        在“reverse_nested”上使用它，并在其上添加路径
        SELECT COUNT(*) FROM index GROUP BY nested(message.info),histogram('field'='comment.likes','reverse_nested'='~comment','interval'='2' , 'alias' = 'someAlias' )

翻译：https://github.com/NLPchina/elasticsearch-sql/wiki/NestedTypes-queries