# Migration from mysql to elasticsearch

**URL:** <https://discuss.elastic.co/t/migration-from-mysql-to-elasticsearch/335577>\
**Category:** Elasticsearch\
**Tags:** migration\
**Created:** [June 8, 2023, 10:10pm UTC](https://discuss.elastic.co/t/migration-from-mysql-to-elasticsearch/335577 "2023-06-08T22:10:03Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Omarbek\_Dinassil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/omarbek_dinassil/32/122049_2.png) [@Omarbek\_Dinassil](https://discuss.elastic.co/u/Omarbek_Dinassil)\
**Post date:** [June 8, 2023, 10:10pm UTC](https://discuss.elastic.co/t/migration-from-mysql-to-elasticsearch/335577/1 "2023-06-08T22:10:03Z")

</div>

Hello!

Right now I have three tables A, B and C in mysql database:

```auto
Table A Table B Table C
id id id       
              FK_A                  
              FK_C

```

I should write a service which returns results from table A giving to it ids from table C:

```auto
request:{
ids_from_tableC: []
}
response:{
ids_from_tableA:[]
}

```

So I have some questions:

1. When I do migration, should I migrate joined query for one indice or two different queries for two indices?
2. If two indices, can I join indices and get desirable result?

It would be cool, if you could help me 🙂

I attempted to write joined query, but there are only one data (1 row), but in MySQL there are more than 10k

---

<div class="post-metadata">

**Author:** ![carly.richmond](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carly.richmond/32/104935_2.png) [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)\
**Post date:** [June 9, 2023, 10:21am UTC](https://discuss.elastic.co/t/migration-from-mysql-to-elasticsearch/335577/2 "2023-06-09T10:21:35Z")

</div>

Hi @Omarbek_Dinassil,

Welcome to the community! I would recommend trying to denormalize your data into a single or the minimum number of indices. So in this case I would try and have the attributes from A, B and C in a single index if you can.

Elasticsearch works differently from a traditional database in the sense that joins over indices are not possible. The alternative if to use enrichment policies or ILM to enrich an index with the fields you need from another.

Hope that helps!

---

<div class="post-metadata">

**Author:** ![Omarbek\_Dinassil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/omarbek_dinassil/32/122049_2.png) [@Omarbek\_Dinassil](https://discuss.elastic.co/u/Omarbek_Dinassil)\
**Post date:** [June 12, 2023, 8:48pm UTC](https://discuss.elastic.co/t/migration-from-mysql-to-elasticsearch/335577/3 "2023-06-12T20:48:32Z")

</div>

@carly.richmond Hello!

Thank you!

I've written joins and group concat to denormalize tables. Now my ES looks like this:

```auto
"hits" : [
      {
        "_index" : "books",
        "_type" : "_doc",
        "_id" : "5838",
        "_score" : 1.0,
        "_source" : {
          "foreign_ids" : "1,2,3",
          "id" : 5838
        }
      },

```

Can I write query to filter by foreign\_ids, for example:

```auto
request:[1,2,3]
response:[5838]

request:[1,2]
response:[5838]

request:[1,4]
response:[]

```

Because, book with id 5838 doesn't contain foreign\_id 4.

Should I convert my string "1,2,3" to array [1,2,3]? If yes, how?

---

<div class="post-metadata">

**Author:** ![carly.richmond](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carly.richmond/32/104935_2.png) [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)\
**Post date:** [June 13, 2023, 9:24am UTC](https://discuss.elastic.co/t/migration-from-mysql-to-elasticsearch/335577/4 "2023-06-13T09:24:46Z")

</div>

Hi @Omarbek_Dinassil,

Pushing multiple values for a field in the document is possible, but you need to consider whether you want to query each element independently, which from your example it looks like you do. I would recommend having a look at the [arrays documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/array.html) which shows how to push documents with this type to a new index. But it would look a bit like this:

```auto
POST array-test/_doc
{
  "foreign_ids" : [1,2,3],
  "id" : 5838
}

```

Once you have them in an array format, a simple match query will allow you to find the documents:

```auto
GET array-test/_search
{
  "_source": ["id"], 
  "query": {
    "match": {
      "foreign_ids": 1
    }
  }
}

```

Hope that helps!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 11, 2023, 9:25am UTC](https://discuss.elastic.co/t/migration-from-mysql-to-elasticsearch/335577/5 "2023-07-11T09:25:39Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.