需求背景
????????很多時(shí)候mysql的表之間是一對(duì)多的關(guān)系，比如庫(kù)信息表（元數(shù)據(jù)信息），表信息表（元數(shù)據(jù)信息），字段信息表（元數(shù)據(jù)信息）。一個(gè)庫(kù)可以包含多個(gè)表，一個(gè)表可以包含多個(gè)字段。他們的關(guān)系：庫(kù)—（1:n）->表—(1:n)->字段。
????????ElasticsSearch（以下簡(jiǎn)稱(chēng)ES）處理這種關(guān)系雖然不是特別擅長(zhǎng)（相對(duì)于關(guān)系型數(shù)據(jù)庫(kù)），因?yàn)镋S和大多數(shù) NoSQL 數(shù)據(jù)庫(kù)類(lèi)似，是扁平化的存儲(chǔ)結(jié)構(gòu)。索引是獨(dú)立文檔的集合體。不同的索引之間一般是沒(méi)有關(guān)系的。
不過(guò)ES目前畢竟發(fā)展到8.x版本了， 已經(jīng)有幾種可選的方式能夠高效的支持這種一對(duì)多關(guān)系的映射。
????????比較常用的方案是嵌套對(duì)象，嵌套文檔和父子文檔。后兩種是我們本文要講的內(nèi)容。
表結(jié)構(gòu)
? ? ? ? 為了便于描述下面的demo內(nèi)容，現(xiàn)在先介紹一下表結(jié)構(gòu)demo內(nèi)容(表名稱(chēng)：字段1，字段2，字段3......)
database: database_id, name, desc
table：table_id,name,desc,address文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-755100.html
column:column_id,name,desc,address文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-755100.html
嵌套文檔查詢實(shí)例
#建立索引元數(shù)據(jù)：兩層嵌套 database->table->column
put http://localhost:9200/test_nested
{
"mappings": {
"properties": {
"table": {
"type": "nested",
"properties": {
"column": {
"type": "nested"
}
}
}
}
}
}
#創(chuàng)建1個(gè)庫(kù)數(shù)據(jù)database1
PUT http://localhost:9200/test_nested/_doc/database1
{
"database_id": 1,
"name" : "database1",
"des" : "This is a database!",
"table" : [
{
"table_id":1,
"name" : "John",
"des" : "This is a table!",
"address":"hangzhou",
"column":[
{
"column_id":1,
"name" :"zhangsan",
"des" : "This is a column!",
"address":"wuhan"
},
{
"column_id":2,
"name" :"Alice",
"des" : "This is a column!",
"address":"changchun"
}
]
},
{
"table_id":2,
"name" : "Alice",
"des" : "This is a table!",
"address":"changchun",
"column":[
{
"column_id":3,
"name" :"zhangsan",
"des" : "This is a column!",
"address":"hangzhou"
},
{
"column_id":4,
"name" :"John",
"des" : "This is a column!",
"address":"zhengzhou"
}
]
}
]
}
#創(chuàng)建1個(gè)庫(kù)數(shù)據(jù)database2
PUT http://localhost:9200/test_nested/_doc/database2
{
"database_id": 2,
"name" : "database2",
"des" : "This is a database!",
"table" : [
{
"table_id":3,
"name" : "zhangsan",
"des" : "This is a table!",
"address":"wuhan",
"column":[
{
"column_id":5,
"name" :"John",
"des" : "This is a column!",
"address":"hangzhou"
},
{
"column_id":6,
"name" :"Alice",
"des" : "This is a column!",
"address":"changchun"
}
]
},
{
"table_id":4,
"name" : "Alice",
"des" : "This is a table!",
"address":"changchun",
"column":[
{
"column_id":7,
"name" :"zhangsan",
"des" : "This is a column!",
"address":"hangzhou"
},
{
"column_id":8,
"name" :"John",
"des" : "This is a column!",
"address":"zhengzhou"
}
]
}
]
}
#嵌套查詢例子，查詢column匹配指定內(nèi)容，且table匹配指定內(nèi)容的文檔
POST http://localhost:9200/test_nested/_search
{
"query" : {
"bool": {
"must": [
{
"nested": {
"path": "table",
"query": {
"bool": {
"must": [
{
"match": {
"table.address": "hangzhou"
}
},
{
"match": {
"table.name": "John"
}
}
]
}
}
}
},
{
"nested": {
"path": "table.column",
"query" : {
"bool": {
"must": [
{
"match": {
"table.column.address": "wuhan"
}
},
{
"match": {
"table.column.name": "zhangsan"
}
}
]
}
}
}
}
]
}
}
}
#實(shí)現(xiàn)類(lèi)似"三表關(guān)聯(lián)查詢+條件過(guò)濾",查詢cloumn匹配指定內(nèi)容，或table匹配指定內(nèi)容，或database匹配指定內(nèi)容的文檔
POST http://localhost:9200/test_nested/_search
{
"query" : {
"bool": {
"should": [
{
"nested": {
"path": "table",
"query": {
"bool": {
"must": [
{
"match": {
"table.address": "hangzhou"
}
},
{
"match": {
"table.name": "John"
}
}
]
}
}
}
},
{
"nested": {
"path": "table.column",
"query" : {
"bool": {
"must": [
{
"match": {
"table.column.address": "hangzhou"
}
},
{
"match": {
"table.column.name": "John"
}
}
]
}
}
}
},
{
"match" :{
"name":"hangzhou"
}
}
]
}
}
}
父子文檔查詢實(shí)例
#創(chuàng)建索引元數(shù)據(jù)
put http://localhost:9200/metadata1
{
"mappings": {
"properties": {
"my_join_field": {
"type": "join",
"relations": {
"database": ["table"],
"table": ["column"]
}
}
}
}
}
#創(chuàng)建1個(gè)父文檔
put http://localhost:9200/metadata1/_doc/1
{
"database_id": "1",
"des": "This is a database!",
"name":"zhangsan",
"address":"hangzhou",
"my_join_field": {
"name": "database"
}
}
#創(chuàng)建1個(gè)子文檔
put http://localhost:9200/metadata1/_doc/2?routing=1
{
"table_id": "1",
"des": "This is a table!",
"name":"lisi",
"address":"hangzhou",
"my_join_field": {
"name": "table",
"parent":1
}
}
#創(chuàng)建1個(gè)孫子文檔
put http://localhost:9200/metadata1/_doc/3?routing=2
{
"column_id": "1",
"des": "This is a column!",
"name":"wangwu",
"address":"hangzhou",
"my_join_field": {
"name": "column",
"parent":2
}
}
#創(chuàng)建1個(gè)孫子文檔
put http://localhost:9200/metadata1/_doc/4?routing=2
{
"column_id": "2",
"des": "This is a column!",
"name":"hangzhou",
"address":"zhengzhou",
"my_join_field": {
"name": "column",
"parent":2
}
}
#創(chuàng)建1個(gè)孫子文檔，用于驗(yàn)證查詢內(nèi)容默認(rèn)分詞了
put http://localhost:9200/metadata1/_doc/5?routing=2
{
"column_id": "3",
"des": "This is a column!",
"name":"hangzhouren",
"address":"hangzhou city",
"my_join_field": {
"name": "column",
"parent":2
}
}
#分頁(yè)查詢某個(gè)字段（查詢范圍包括父，子，孫子文檔）
post http://localhost:9200/metadata1/_search
{
"query" : {
"match": {
"address" : "hangzhou"
}
},
"from" : 1,
"size" : 1
}
#term 批量查詢
post http://localhost:9200/metadata1/_search
{
"query": {
"terms" : {
"address":["hangzhou pro","zhengzhou"]
}
}
}
#查詢具備滿足匹配內(nèi)容的孫子文檔的子文檔
post http://localhost:9200/metadata1/_search
{
"query": {
"has_child": {
"type": "column",
"query" : {
"match": {
"address" : "hangzhou"
}
}
}
}
}
#查詢具備滿足匹配內(nèi)容的子文檔的父文檔
post http://localhost:9200/metadata1/_search
{
"query": {
"has_child": {
"type": "table",
"query" : {
"match": {
"address" : "hangzhou"
}
}
}
}
}
#查詢具備滿足匹配內(nèi)容的孫子文檔的父文檔
post http://localhost:9200/metadata1/_search
{
"query": {
"has_child": {
"type": "table",
"query" : {
"has_child": {
"type": "column",
"query" : {
"multi_match": {
"query" : "hangzhou",
"fields":["address","name"]
}
}
}
}
}
}
}
#bool查詢滿足條件孫子文檔的父文檔，和滿足條件子文檔的父文檔
post http://localhost:9200/metadata1/_search
{
"query": {
"bool": {
"should": [
{
"has_child": {
"type": "table",
"query" : {
"has_child": {
"type": "column",
"query" : {
"multi_match": {
"query" : "hangzhou",
"fields":["address","name"]
}
}
}
}
}
},
{
"has_child": {
"type": "table",
"query" : {
"multi_match": {
"query" : "hangzhou",
"fields":["address","name"]
}
}
}
}
]
}
}
}
#查詢滿足條件子文檔的父文檔的子文檔，即子文檔本身；如果父，子，孫文檔的文檔字段名稱(chēng)不同，就不用這么麻煩的查詢
post http://localhost:9200/metadata1/_search
{
"query": {
"has_parent": {
"parent_type": "database",
"query" : {
"has_child": {
"type": "table",
"query" : {
"multi_match": {
"query" : "hangzhou",
"fields":["address","name"]
}
}
}
}
}
}
}
#以下兩條查詢可以類(lèi)似實(shí)現(xiàn)"三表關(guān)聯(lián)查詢+條件過(guò)濾"的功能
#先查詢滿足條件匹配的父文檔的子文檔，滿足條件匹配孫子文檔的子文檔和滿足條件匹配的子文檔
post http://localhost:9200/metadata1/_search
{
"query": {
"bool": {
"should": [
{
"has_parent": {
"parent_type": "database",
"query" : {
"multi_match": {
"query" : "hangzhou",
"fields":["address","name"]
}
}
}
},
{
"has_child": {
"type": "column",
"query" : {
"multi_match": {
"query" : "hangzhou",
"fields":["address","name"]
}
}
}
},
{
"has_parent": {
"parent_type": "database",
"query" : {
"has_child": {
"type": "table",
"query" : {
"multi_match": {
"query" : "hangzhou",
"fields":["address","name"]
}
}
}
}
}
}
]
}
}
}
#根據(jù)上面的子文檔查詢關(guān)聯(lián)的父文檔和孫子文檔，然后再在程序里進(jìn)行數(shù)據(jù)關(guān)聯(lián)組裝
post http://localhost:9200/metadata1/_search
{
"query": {
"bool": {
"should": [
{
"has_parent": {
"parent_type": "table",
"query" : {
"ids": {
"values" : [2]
}
}
}
},
{
"has_child": {
"type": "table",
"query" : {
"ids": {
"values" : [2]
}
}
}
}
]
}
}
}
到了這里，關(guān)于ES實(shí)現(xiàn)三表關(guān)聯(lián)查詢+條件過(guò)濾的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
