Creating a new index for search purpose fom three other indexes with elastic search java api

Question

We have a requirement to search data on multiple indexes. We tried to club them into one through hibernate search orm, it went well but this composite index is not update once the actual database table is updated. Though individual indexes are updated successfully. So, we though to change our strategy from hibernate orm to elastic search java api.

To achieve this i was thinking If I could get any logic to merge three different indexes into one before firing the search query. So that I could get data from all the three indexes. These index do have a common field, which could be treated as id field. May I can relate these on this id field and retrieve relational data from these indexes, post merging them into one.

Below is the index definition for the three indexes I want to merge.

basclt0100:

   {
  "basclt0100" : {
    "aliases" : { },
    "mappings" : {
      "com.csc.pt.svc.data.to.Basclt0100TO" : {
        "dynamic" : "strict",
        "properties" : {
          "clientname" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "nameAnalyzer"
          },
          "cltseqnum" : {
            "type" : "long",
            "store" : true
          },
          "firstname" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "nameAnalyzer"
          },
          "id" : {
            "type" : "keyword",
            "store" : true
          },
          "longname" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "nameAnalyzer"
          },
          "midname" : {
            "type" : "text",
            "store" : true
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "number_of_shards" : "5",
        "provided_name" : "basclt0100",
        "creation_date" : "1536086417001",
        "analysis" : {
          "analyzer" : {
            "nameAnalyzer" : {
              "filter" : [
                "lowercase"
              ],
              "tokenizer" : "keyword"
            }
          }
        },
        "number_of_replicas" : "1",
        "uuid" : "YKCtVIaCQjatBeb2g1JfUA",
        "version" : {
          "created" : "6030299"
        }
      }
    }
  }
}

basclt0300

    {
  "basclt0300" : {
    "aliases" : { },
    "mappings" : {
      "com.csc.pt.svc.data.to.Basclt0300TO" : {
        "dynamic" : "strict",
        "properties" : {
          "addrln1" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "addressAnalyzer"
          },
          "addrln2" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "addressAnalyzer"
          },
          "addrln3" : {
            "type" : "text",
            "store" : true
          },
          "addrseqnum" : {
            "type" : "text",
            "store" : true
          },
          "city" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "addressAnalyzer"
          },
          "cltseqnum" : {
            "type" : "long",
            "store" : true
          },
          "country" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "addressAnalyzer"
          },
          "id" : {
            "type" : "keyword",
            "store" : true
          },
          "state" : {
            "type" : "text",
            "store" : true
          },
          "zipcode" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "addressAnalyzer"
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "number_of_shards" : "5",
        "provided_name" : "basclt0300",
        "creation_date" : "1536086426461",
        "analysis" : {
          "analyzer" : {
            "addressAnalyzer" : {
              "filter" : [
                "standard",
                "lowercase",
                "asciifolding"
              ],
              "tokenizer" : "standard"
            }
          }
        },
        "number_of_replicas" : "1",
        "uuid" : "irxvUu2qR3udpgJUE0NoSA",
        "version" : {
          "created" : "6030299"
        }
      }
    }
  }
}

basclt0900

{
  "basclt0900" : {
    "aliases" : { },
    "mappings" : {
      "com.csc.pt.svc.data.to.Basclt0900TO" : {
        "dynamic" : "strict",
        "properties" : {
          "cltseqnum" : {
            "type" : "long",
            "store" : true
          },
          "email1" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "emailAnalyzer"
          },
          "email2" : {
            "type" : "text",
            "store" : true,
            "analyzer" : "emailAnalyzer"
          },
          "id" : {
            "type" : "keyword",
            "store" : true
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "number_of_shards" : "5",
        "provided_name" : "basclt0900",
        "creation_date" : "1536086423657",
        "analysis" : {
          "analyzer" : {
            "emailAnalyzer" : {
              "filter" : [
                "lowercase"
              ],
              "tokenizer" : "classic"
            }
          }
        },
        "number_of_replicas" : "1",
        "uuid" : "dmzw9ZswTwCNVvne-FAt2w",
        "version" : {
          "created" : "6030299"
        }
      }
    }
  }
}

please help with some iogic/strategy to achieve this.

Once more we would like to search on all fields of these three indexes and retrieve the associated data for a search. Primary key would be the cltseqnum available under all the three indexes.

Answer 1

Achieved this through the @IndexedEmbedded and @ContainedIn annotations and creating OneToMany relationships under our .hbm files.

Also, our database is not so relational, otherwise it would have been a piece of cake after using these annotations. I had to do some workaround to make the insert logic work for the combined index(Single index from three different tables.).Though this logic does have some restrictions/issues with insert part, hoping HibernateSearch team will surely cover up these annotations more briefly in next releases.