ElasticSearch Multi Index Query

Question

simple question: I have multiple indexes in my elasticsearch engine mirrored by postgresql using logstash. ElasticSearch performs well for fuzzy searches, but now I need to use references within the indexes, that need to be handled by the queries.

Index A:
{
  name: "alice",
  _id: 5
} 
...

Index B:
{
  name: "bob",
  _id: 3,
  best_friend: 5
}
...

How do I query:

Get every match of index B with field name starting with "b" and index A referenced by "best_friend" with the name starting with "a"

Is this even possible with elasticsearch?

Answer 1

Yes, that's possible: POST A,B/_search will query multiple indexes.

In order to match a record from a specific index, you can use meta-data field _index

Below is a query that gets every match of index B with field name starting with "b" and index A with the name starting with "a" but not matches a reference as you usually do in relational SQL databases. foreign key reference matching (join) in Elastic and every NoSQL is YOUR responsibility AFAIK. refer to Elastic Definitive Guide to find out the best approach to your needs. Lastly, NoSQL is not SQL, change your mind.

POST A,B/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "prefix": {
                  "name": "a"
                }
              },
              {
                "term": {
                  "_index": "A"
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "prefix": {
                  "name": "b"
                }
              },
              {
                "term": {
                  "_index": "B"
                }
              }
            ]
          }
        }
      ]
    }
  }
}