ElasticSearch data modeling with Mysql

Question

I'm studying Elastic search and trying to modeling with mysql tables. I have Mysql tables below for example.

Book Table

• id
• title
• abstract
• publisher_id

ex) book table

---

id | title | abstract | publisher_id

3 | book title A | A book about elastic search. | 12

---

Authors Table

• id
• name
• country
• book_id(id from Book table)

ex) authors table

---

id | name | country | book_id

1 | Alex | Korea | 3

2 | John | USA | 3

---

author could be more than one person.

Publisher Table

• id
• name

ex) publisher table

---

id | name

12 | Packt pub

---

In my thoughts, i could convert like below for elastic search index.

Book index

• id int
• title string
• abstract string
• authors array (id from Authors index. Authors could be more than one.)
• publisher int (id form publisher index)

Authors index

• id int
• name string
• country string

Publisher index

• id int
• name string

What i need to do is, search for Book title and abstract and get author's id. And then show authors list. For mysql, i would do like this.

Select * from authors where id in (select authors_id from book where match(title,abstract) against('${keyword}' IN BOOLEAN MODE))

How can i do this for elastic search? Is there a better way to modeling? and I also want to know how to query First search authors ids from book index and search with these ids from authors again?? or any other solution???

Answer 1

This is easy to achieve using a single index in Elasticsearch(ES) as pointed by other ES experts and I may not be able to give the proper ES query but my below example gives you idea, on how to model your data and query it.

Index mapping

{
  "mappings": {
    "properties": {
      "title": {
        "type": "text"
      },
      "abstract":{
        "type" : "text"
      },
      "author" :{
        "type" : "text",
        "fielddata" : true // instead of this, you can use `keyword` field for better perf, this is just for ex
      }
    }
  }
}

Index sample docs

{
  "title" : "hello world",
  "abstract" : "hello world is common in computer programming",
  "author" : ["sehun, stackoverflow"]
}

{
  "title" : "foo bar",
  "abstract" : "foo bar is common in computer programming",
  "author" : ["opster, stackoverflow"]
}

Search query to search on title and abstract and agg on author field

{
  "query": {
    "multi_match": {
      "query": "common",
      "fields": [
        "title",
        "abstract"
      ]
    }
  },
  "aggs": {
    "Cities": {
      "terms": {
        "field": "author"
      }
    }
  }
}

Search results

hits": [
      {
        "_index": "internaledgepre",
        "_type": "_doc",
        "_id": "1",
        "_score": 0.18232156,
        "_source": {
          "title": "foo bar",
          "abstract": "foo bar is common in computer programming",
          "author": [
            "opster, stackoverflow"
          ]
        }
      },
      {
        "_index": "internaledgepre",
        "_type": "_doc",
        "_id": "2",
        "_score": 0.18232156,
        "_source": {
          "title": "hello world",
          "abstract": "hello world is common in computer programming",
          "author": [
            "sehun, stackoverflow"
          ]
        }
      }
    ]
  },
  "aggregations": {
    "Cities": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "stackoverflow",
          "doc_count": 2
        },
        {
          "key": "opster",
          "doc_count": 1
        },
        {
          "key": "sehun",
          "doc_count": 1
        }
      ]
    }
  }