ES 中join的使用

付威     2019-02-21   3787   10min  

引出问题

在mysql中,可以使用join来实现表与表之间的数据连接,在es中如何实现这个问题?

相对于mysql来说,es有几个不同的地方

  1. 不支持跨index的join
  2. 一个index只能包含一个类型
  3. 分布式的存储方式,对于数据的搜寻造成障碍

对于上面的几个问题,es的解决方案是**在一个索引下,借助父子关系,实现类似Mysql中多表关联的操作**

定义类型和join索引

PUT myorder
{
 "mappings": {
   "_doc": {
     "properties": {
       "order_join": { 
         "type": "join",
         "relations": {
           "order": "suborder" 
          }
        }
      }
    }
  }
}

定义join关系为order_join,其中order是父文档,suborder是子文档。

put  myorder/_mapping/_doc
{
    "properties": {
    "orderId": {
        "type": "keyword"
    },
    "shortTime": {
        "type": "date"
    },
    "name": {
        "type": "keyword"
    },
    "amount": {
        "type": "double"
    },
    "desc": {
        "type": "text"
    }
  }
}

插入主单数据

PUT myorder/_doc/10001
{
  "shortTime": "2019-01-05",
  "orderId": "10001",
  "name": "user2",
  "amount": 123.09,
  "desc": "其他收入",
  "order_join": "order"
}

order_join定义为order类型

插入子单数据

使用自定义ID用PUT方法

POST myorder/_doc?routing=1
{
  "shortTime": "2019-01-05",
  "orderId": "10001",
  "name": "user2",
  "amount": 12.09,
  "desc": "收入",
  "order_join": {
     "name": "suborder",
     "parent":"10001"
   }
}

POST myorder/_doc?routing=1
{
  "shortTime": "2019-01-05",
  "orderId": "10002",
  "name": "user2",
  "amount": 122.09,
  "desc": "收入",
  "order_join": {
      "name": "suborder",
     "parent":"10001"
   }
}

建立父子关系索引,routing 参数是必须的,因为父子文档必须在同一个分片上

查询主单

GET myorder/_search
{
    "query": {
        "has_child" : {
            "type" : "suborder",
            "query" : {
                "match_all" : {
                    
                }
            }
        }
    }
}

查询子单

GET myorder/_search
{
    "query": {
        "has_parent" : {
            "parent_type" : "order",
            "query" : {
                "match_all" : {
                    
                }
            }
        }
    }
}

聚合查询

  • 主单聚合

    GET myorder/_search
    {
      "query": {
        "parent_id": {
          "type": "suborder",
          "id": "10001"
        }
      },
      "aggs": {
        "parents12312": {
          "terms": {
            "field": "order_join#order"
          },
          "aggs": {
            "sumAmount": {
              "stats": {
                "field": "amount"
              }
            }
          }
        }
      }
    }
    
    
  • 子单聚合

    
      GET myorder/_search
      {
        "size": 0, 
        "aggs": {
          "parent": {
            "children": {
              "type": "suborder"
            },
            "aggs": {
              "sumAmount": {
                "stats": {
                  "field": "amount"
                }
              }
            }
          }
        }
      }
    
  • 聚合加筛选:

      GET myorder/_search
      {
            "query": {
              "has_child" : {
                  "type" : "suborder",
                  "query" : {
                      "match_all" : {
                            
                      }
                  }
              }
          },
        "aggs": {
          "parent": {
            "children": {
              "type": "suborder"
            },
            "aggs": {
              "fields": {
                "terms": {
                  "field": "orderId"
                },
                "aggs": {
                  "sumAmount": {
                    "sum": {
                      "field": "amount"
                    }
                  },
                  "having": {
                    "bucket_selector": {
                      "buckets_path": {
                        "orderCount": "_count",
                        "sumAmount": "sumAmount"
                      },
                      "script": {
                        "source": "params.sumAmount >= 100 && params.orderCount >=0"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    

定义一对多的索引


一对一的索引模型很难满足日常业务的数据处理,es也支持一对多的join

PUT myorder
{
 "mappings": {
   "_doc": {
     "properties": {
       "order_join": { 
         "type": "join",
         "relations": {
           "order": ["suborder1", "suborder2"],   
           "suborder2":"suborder3"
          }
        }
      }
    }
  }
}

上面的索引的关联的关系如下:

       order
       /   \
 suborder1  suborder2  
              \ 
              suborder3 
(本文完)

作者:付威

博客地址:http://blog.laofu.online

如果觉得对您有帮助,可以下方的RSS订阅,谢谢合作

如有任何知识产权、版权问题或理论错误,还请指正。

本文是付威的网络博客原创,自由转载-非商用-非衍生-保持署名,请遵循:创意共享3.0许可证

交流请加群113249828: 点击加群   或发我邮件 laofu_online@163.com

付威

获得最新的博主文章,请关注上方公众号