# Joint database table and an array

**URL:** <https://discuss.elastic.co/t/joint-database-table-and-an-array/44839>\
**Category:** Logstash\
**Created:** [March 18, 2016, 1:34pm UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839 "2016-03-18T13:34:44Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [March 18, 2016, 1:34pm UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839/1 "2016-03-18T13:34:44Z")

</div>

I have a database table and a document array

```
+---------+--------+
| m_id | state |
+---------+--------+
| m1 | s1 |
| m2 | s2 |
| m3 | s1 |
+---------+--------+

state_array = array(s1=>'tamilnadu',s2=>'kerala')

```

How to joint and create a single type in elastic search using logstash.

```
+---------+------------+
| m_id | state |
+---------+------------+
| m1 | tamilnadu |
| m2 | kerala |
| m3 | tamilnadu |
+---------+------------+

```

Give me any suggestions for this kind of situation.  
My core process is analyzing this kind of data by Kibana.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [March 19, 2016, 3:45pm UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839/2 "2016-03-19T15:45:35Z")

</div>

I suspect the translate filter can help you.

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [March 23, 2016, 5:48am UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839/3 "2016-03-23T05:48:15Z")

</div>

Would you please give me an example for the same

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [March 23, 2016, 6:35am UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839/4 "2016-03-23T06:35:15Z")

</div>

Untested:

```auto
translate {
  dictionary => {
    "s1" => "tamilnadu"
    "s2" => "kerala"
  }
  field => "state"
  destination => "state"
}

```

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [March 23, 2016, 7:41am UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839/5 "2016-03-23T07:41:37Z")

</div>

Thank you Magnus.  
I tested it, `destination` is not work.  
so I try this for the following table and configuration.

```
+-----------+----------+---------+
| Name | gender | state |
+-----------+----------+---------+
| croos | 1 | 2 |
| priya | 2 | 1 |
+-----------+----------+---------+  

filter {
  translate {
    dictionary => {
      "1" => "male"
      "2" => "female"
    }
    field => "gender"
  }

  mutate {  
    rename => { "translation" => "gender" }
  }
}

filter {
  translate {
    dictionary => {
      "1" => "tamilnadu"
      "2" => "karnadaka"
    }
    field => "state"
  }

  mutate {    
    rename => { "translation" => "state" }
  }
}

```

it is work fine. The output is :

```
{
            "id" => 1,
          "name" => "croos",
        "gender" => "male",
         "state" => "karnadaka",
      "@version" => "1",
    "@timestamp" => "2016-03-23T07:30:40.322Z"
}
{
            "id" => 2,
          "name" => "priya",
        "gender" => "female",
         "state" => "tamilnadu",
      "@version" => "1",
    "@timestamp" => "2016-03-23T07:30:40.323Z"
}

```

I think that is not a proper way to do this. Any suggestions for do the same with in a single filter.

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [March 23, 2016, 8:48am UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839/6 "2016-03-23T08:48:56Z")

</div>

Now it is work fine. Thank you very much Magnus.

```
filter {
  translate {
    dictionary => {
      "1" => "tamilnadu"
      "2" => "karnadaka"
    }
    field => "state"
    destination=>"state1"
  }

  translate {
    dictionary => {
      "1" => "male"
      "2" => "female"
    }
    field => "gender"
    destination=>"gender1"
  }

mutate {    
      remove_field=>["gender","state"]
    }
}
```

---

<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 6, 2017, 5:05am UTC](https://discuss.elastic.co/t/joint-database-table-and-an-array/44839/7 "2017-07-06T05:05:46Z")

</div>

Closed on July 6, 2017, 5:05am UTC