• ES实现三表关联查询+条件过滤


    需求背景

            很多时候mysql的表之间是一对多的关系,比如库信息表(元数据信息),表信息表(元数据信息),字段信息表(元数据信息)。一个库可以包含多个表,一个表可以包含多个字段。他们的关系:库—(1:n)->表—(1:n)->字段。

            ElasticsSearch(以下简称ES)处理这种关系虽然不是特别擅长(相对于关系型数据库),因为ES和大多数 NoSQL 数据库类似,是扁平化的存储结构。索引是独立文档的集合体。不同的索引之间一般是没有关系的。

    不过ES目前毕竟发展到8.x版本了, 已经有几种可选的方式能够高效的支持这种一对多关系的映射。

            比较常用的方案是嵌套对象,嵌套文档和父子文档。后两种是我们本文要讲的内容。

    表结构

            为了便于描述下面的demo内容,现在先介绍一下表结构demo内容(表名称:字段1,字段2,字段3......)

    database: database_id, name, desc

    table:table_id,name,desc,address

    column:column_id,name,desc,address

    嵌套文档查询实例

    1. #建立索引元数据:两层嵌套 database->table->column
    2. put http://localhost:9200/test_nested
    3. {
    4. "mappings": {
    5. "properties": {
    6. "table": {
    7. "type": "nested",
    8. "properties": {
    9. "column": {
    10. "type": "nested"
    11. }
    12. }
    13. }
    14. }
    15. }
    16. }
    17. #创建1个库数据database1
    18. PUT http://localhost:9200/test_nested/_doc/database1
    19. {
    20. "database_id": 1,
    21. "name" : "database1",
    22. "des" : "This is a database!",
    23. "table" : [
    24. {
    25. "table_id":1,
    26. "name" : "John",
    27. "des" : "This is a table!",
    28. "address":"hangzhou",
    29. "column":[
    30. {
    31. "column_id":1,
    32. "name" :"zhangsan",
    33. "des" : "This is a column!",
    34. "address":"wuhan"
    35. },
    36. {
    37. "column_id":2,
    38. "name" :"Alice",
    39. "des" : "This is a column!",
    40. "address":"changchun"
    41. }
    42. ]
    43. },
    44. {
    45. "table_id":2,
    46. "name" : "Alice",
    47. "des" : "This is a table!",
    48. "address":"changchun",
    49. "column":[
    50. {
    51. "column_id":3,
    52. "name" :"zhangsan",
    53. "des" : "This is a column!",
    54. "address":"hangzhou"
    55. },
    56. {
    57. "column_id":4,
    58. "name" :"John",
    59. "des" : "This is a column!",
    60. "address":"zhengzhou"
    61. }
    62. ]
    63. }
    64. ]
    65. }
    66. #创建1个库数据database2
    67. PUT http://localhost:9200/test_nested/_doc/database2
    68. {
    69. "database_id": 2,
    70. "name" : "database2",
    71. "des" : "This is a database!",
    72. "table" : [
    73. {
    74. "table_id":3,
    75. "name" : "zhangsan",
    76. "des" : "This is a table!",
    77. "address":"wuhan",
    78. "column":[
    79. {
    80. "column_id":5,
    81. "name" :"John",
    82. "des" : "This is a column!",
    83. "address":"hangzhou"
    84. },
    85. {
    86. "column_id":6,
    87. "name" :"Alice",
    88. "des" : "This is a column!",
    89. "address":"changchun"
    90. }
    91. ]
    92. },
    93. {
    94. "table_id":4,
    95. "name" : "Alice",
    96. "des" : "This is a table!",
    97. "address":"changchun",
    98. "column":[
    99. {
    100. "column_id":7,
    101. "name" :"zhangsan",
    102. "des" : "This is a column!",
    103. "address":"hangzhou"
    104. },
    105. {
    106. "column_id":8,
    107. "name" :"John",
    108. "des" : "This is a column!",
    109. "address":"zhengzhou"
    110. }
    111. ]
    112. }
    113. ]
    114. }
    115. #嵌套查询例子,查询column匹配指定内容,且table匹配指定内容的文档
    116. POST http://localhost:9200/test_nested/_search
    117. {
    118. "query" : {
    119. "bool": {
    120. "must": [
    121. {
    122. "nested": {
    123. "path": "table",
    124. "query": {
    125. "bool": {
    126. "must": [
    127. {
    128. "match": {
    129. "table.address": "hangzhou"
    130. }
    131. },
    132. {
    133. "match": {
    134. "table.name": "John"
    135. }
    136. }
    137. ]
    138. }
    139. }
    140. }
    141. },
    142. {
    143. "nested": {
    144. "path": "table.column",
    145. "query" : {
    146. "bool": {
    147. "must": [
    148. {
    149. "match": {
    150. "table.column.address": "wuhan"
    151. }
    152. },
    153. {
    154. "match": {
    155. "table.column.name": "zhangsan"
    156. }
    157. }
    158. ]
    159. }
    160. }
    161. }
    162. }
    163. ]
    164. }
    165. }
    166. }
    167. #实现类似"三表关联查询+条件过滤",查询cloumn匹配指定内容,或table匹配指定内容,或database匹配指定内容的文档
    168. POST http://localhost:9200/test_nested/_search
    169. {
    170. "query" : {
    171. "bool": {
    172. "should": [
    173. {
    174. "nested": {
    175. "path": "table",
    176. "query": {
    177. "bool": {
    178. "must": [
    179. {
    180. "match": {
    181. "table.address": "hangzhou"
    182. }
    183. },
    184. {
    185. "match": {
    186. "table.name": "John"
    187. }
    188. }
    189. ]
    190. }
    191. }
    192. }
    193. },
    194. {
    195. "nested": {
    196. "path": "table.column",
    197. "query" : {
    198. "bool": {
    199. "must": [
    200. {
    201. "match": {
    202. "table.column.address": "hangzhou"
    203. }
    204. },
    205. {
    206. "match": {
    207. "table.column.name": "John"
    208. }
    209. }
    210. ]
    211. }
    212. }
    213. }
    214. },
    215. {
    216. "match" :{
    217. "name":"hangzhou"
    218. }
    219. }
    220. ]
    221. }
    222. }
    223. }

    父子文档查询实例

    1. #创建索引元数据
    2. put http://localhost:9200/metadata1
    3. {
    4. "mappings": {
    5. "properties": {
    6. "my_join_field": {
    7. "type": "join",
    8. "relations": {
    9. "database": ["table"],
    10. "table": ["column"]
    11. }
    12. }
    13. }
    14. }
    15. }
    16. #创建1个父文档
    17. put http://localhost:9200/metadata1/_doc/1
    18. {
    19. "database_id": "1",
    20. "des": "This is a database!",
    21. "name":"zhangsan",
    22. "address":"hangzhou",
    23. "my_join_field": {
    24. "name": "database"
    25. }
    26. }
    27. #创建1个子文档
    28. put http://localhost:9200/metadata1/_doc/2?routing=1
    29. {
    30. "table_id": "1",
    31. "des": "This is a table!",
    32. "name":"lisi",
    33. "address":"hangzhou",
    34. "my_join_field": {
    35. "name": "table",
    36. "parent":1
    37. }
    38. }
    39. #创建1个孙子文档
    40. put http://localhost:9200/metadata1/_doc/3?routing=2
    41. {
    42. "column_id": "1",
    43. "des": "This is a column!",
    44. "name":"wangwu",
    45. "address":"hangzhou",
    46. "my_join_field": {
    47. "name": "column",
    48. "parent":2
    49. }
    50. }
    51. #创建1个孙子文档
    52. put http://localhost:9200/metadata1/_doc/4?routing=2
    53. {
    54. "column_id": "2",
    55. "des": "This is a column!",
    56. "name":"hangzhou",
    57. "address":"zhengzhou",
    58. "my_join_field": {
    59. "name": "column",
    60. "parent":2
    61. }
    62. }
    63. #创建1个孙子文档,用于验证查询内容默认分词了
    64. put http://localhost:9200/metadata1/_doc/5?routing=2
    65. {
    66. "column_id": "3",
    67. "des": "This is a column!",
    68. "name":"hangzhouren",
    69. "address":"hangzhou city",
    70. "my_join_field": {
    71. "name": "column",
    72. "parent":2
    73. }
    74. }
    75. #分页查询某个字段(查询范围包括父,子,孙子文档)
    76. post http://localhost:9200/metadata1/_search
    77. {
    78. "query" : {
    79. "match": {
    80. "address" : "hangzhou"
    81. }
    82. },
    83. "from" : 1,
    84. "size" : 1
    85. }
    86. #term 批量查询
    87. post http://localhost:9200/metadata1/_search
    88. {
    89. "query": {
    90. "terms" : {
    91. "address":["hangzhou pro","zhengzhou"]
    92. }
    93. }
    94. }
    95. #查询具备满足匹配内容的孙子文档的子文档
    96. post http://localhost:9200/metadata1/_search
    97. {
    98. "query": {
    99. "has_child": {
    100. "type": "column",
    101. "query" : {
    102. "match": {
    103. "address" : "hangzhou"
    104. }
    105. }
    106. }
    107. }
    108. }
    109. #查询具备满足匹配内容的子文档的父文档
    110. post http://localhost:9200/metadata1/_search
    111. {
    112. "query": {
    113. "has_child": {
    114. "type": "table",
    115. "query" : {
    116. "match": {
    117. "address" : "hangzhou"
    118. }
    119. }
    120. }
    121. }
    122. }
    123. #查询具备满足匹配内容的孙子文档的父文档
    124. post http://localhost:9200/metadata1/_search
    125. {
    126. "query": {
    127. "has_child": {
    128. "type": "table",
    129. "query" : {
    130. "has_child": {
    131. "type": "column",
    132. "query" : {
    133. "multi_match": {
    134. "query" : "hangzhou",
    135. "fields":["address","name"]
    136. }
    137. }
    138. }
    139. }
    140. }
    141. }
    142. }
    143. #bool查询满足条件孙子文档的父文档,和满足条件子文档的父文档
    144. post http://localhost:9200/metadata1/_search
    145. {
    146. "query": {
    147. "bool": {
    148. "should": [
    149. {
    150. "has_child": {
    151. "type": "table",
    152. "query" : {
    153. "has_child": {
    154. "type": "column",
    155. "query" : {
    156. "multi_match": {
    157. "query" : "hangzhou",
    158. "fields":["address","name"]
    159. }
    160. }
    161. }
    162. }
    163. }
    164. },
    165. {
    166. "has_child": {
    167. "type": "table",
    168. "query" : {
    169. "multi_match": {
    170. "query" : "hangzhou",
    171. "fields":["address","name"]
    172. }
    173. }
    174. }
    175. }
    176. ]
    177. }
    178. }
    179. }
    180. #查询满足条件子文档的父文档的子文档,即子文档本身;如果父,子,孙文档的文档字段名称不同,就不用这么麻烦的查询
    181. post http://localhost:9200/metadata1/_search
    182. {
    183. "query": {
    184. "has_parent": {
    185. "parent_type": "database",
    186. "query" : {
    187. "has_child": {
    188. "type": "table",
    189. "query" : {
    190. "multi_match": {
    191. "query" : "hangzhou",
    192. "fields":["address","name"]
    193. }
    194. }
    195. }
    196. }
    197. }
    198. }
    199. }
    200. #以下两条查询可以类似实现"三表关联查询+条件过滤"的功能
    201. #先查询满足条件匹配的父文档的子文档,满足条件匹配孙子文档的子文档和满足条件匹配的子文档
    202. post http://localhost:9200/metadata1/_search
    203. {
    204. "query": {
    205. "bool": {
    206. "should": [
    207. {
    208. "has_parent": {
    209. "parent_type": "database",
    210. "query" : {
    211. "multi_match": {
    212. "query" : "hangzhou",
    213. "fields":["address","name"]
    214. }
    215. }
    216. }
    217. },
    218. {
    219. "has_child": {
    220. "type": "column",
    221. "query" : {
    222. "multi_match": {
    223. "query" : "hangzhou",
    224. "fields":["address","name"]
    225. }
    226. }
    227. }
    228. },
    229. {
    230. "has_parent": {
    231. "parent_type": "database",
    232. "query" : {
    233. "has_child": {
    234. "type": "table",
    235. "query" : {
    236. "multi_match": {
    237. "query" : "hangzhou",
    238. "fields":["address","name"]
    239. }
    240. }
    241. }
    242. }
    243. }
    244. }
    245. ]
    246. }
    247. }
    248. }
    249. #根据上面的子文档查询关联的父文档和孙子文档,然后再在程序里进行数据关联组装
    250. post http://localhost:9200/metadata1/_search
    251. {
    252. "query": {
    253. "bool": {
    254. "should": [
    255. {
    256. "has_parent": {
    257. "parent_type": "table",
    258. "query" : {
    259. "ids": {
    260. "values" : [2]
    261. }
    262. }
    263. }
    264. },
    265. {
    266. "has_child": {
    267. "type": "table",
    268. "query" : {
    269. "ids": {
    270. "values" : [2]
    271. }
    272. }
    273. }
    274. }
    275. ]
    276. }
    277. }
    278. }

  • 相关阅读:
    Java Map中如何获取Map集合中所有value呢?
    docker:已启动容器修改添加端口映射
    postswigger 靶场(CSRF)攻略-- 1.没有防御措施的 CSRF 漏洞
    JVM 相关知识整理
    剑指 Offer 04. 二维数组中的查找
    qt中对话框
    从零搭建个人博客项目并通过github部署上线
    python poetry的教程
    Angular实现日期,数据和更新
    详解欧拉计划第185题:数字头脑
  • 原文地址:https://blog.csdn.net/lgq2016/article/details/133878695