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

NoSQL doesn’t support joins like SQL supports. So all data should be indexed in one document.

Do go through nosql datamodelling, architecture, significances and also learn how it’s different from SQL.

Answer 2

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
        }
      ]
    }
  }