# One to Many table relationship pre-processing

**URL:** <https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007>\
**Category:** Elasticsearch\
**Created:** [May 21, 2019, 1:15pm UTC](https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007 "2019-05-21T13:15:57Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jdemp0913](https://avatars.discourse-cdn.com/v4/letter/j/5fc32e/32.png) [@jdemp0913](https://discuss.elastic.co/u/jdemp0913)\
**Post date:** [May 21, 2019, 1:15pm UTC](https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007/1 "2019-05-21T13:15:57Z")

</div>

We have a use case where we need to query and join two database tables in a 1-to-many relationship and upload and index those documents (records) to ElasticSearch every 3 minutes for use by a Kibana dashboard.

The total number of documents (records) that need to be indexed is ~240,000 every three minutes.

My question is:

1. Where is the best place to do this join pre-processing? In Logstash using a jdbc plug-in or in a c# application that does the query, join and converts the result set to JSON and then uses NEXT to bulk upload the JSON to ElasticSearch.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 21, 2019, 2:01pm UTC](https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007/2 "2019-05-21T14:01:28Z")

</div>

Hi John,  
If you share a common field e.g. "customer\_id" one way to do an efficient join purely in elasticsearch is using a sorted [scroll](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-scroll.html) across 2 indices e.g:

```
DELETE customers
POST customers/_doc/_bulk
{"index":{}}
{"customer_id": 1,"name": "Fred"}
{"index":{}}
{"customer_id": 2,"name": "Jim"}

DELETE orders
POST orders/_doc/_bulk
{"index":{}}
{"customer_id": 1,"order_value": 11}
{"index":{}}
{"customer_id": 2,"order_value": 22}
{"index":{}}
{"customer_id": 1,"order_value": 111}

POST customers,orders/_search?scroll=1m
{
  "sort": [
	{
	  "customer_id": {
		"order": "desc"
	  }
	}
  ]
}

```

A bespoke client script would process this stream, fusing docs that share a common `customer_id` into a single document which can then be added using a bulk index operation.

---

<div class="post-metadata">

**Author:** ![jdemp0913](https://avatars.discourse-cdn.com/v4/letter/j/5fc32e/32.png) [@jdemp0913](https://discuss.elastic.co/u/jdemp0913)\
**Post date:** [May 21, 2019, 8:57pm UTC](https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007/3 "2019-05-21T20:57:32Z")

</div>

Hi Mark, Thank you for your response. I have to admit I am new to Elastic Search, so I apologize in advance for any silly statements/questions.

Yes, I have a common field.

Using your example, what I am trying to do is combine 10,000 customers who have 25 orders each for a total of about 250,000 records (documents). The data currently exists in two database tables which is queried every three minutes. I need to refresh (re-index) all 250,000 records every 3 minutes (or shorter if possible), because the data in the orders is changing.

I am looking for the best way to bulk upload this data to ElasticSearch.

I do not know how to

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 21, 2019, 9:13pm UTC](https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007/4 "2019-05-21T21:13:24Z")

</div>

It will probably involve writing some code using your favourite choice of language. We have client side libraries for a wide variety of languages that will call the apis I mentioned

---

<div class="post-metadata">

**Author:** ![jdemp0913](https://avatars.discourse-cdn.com/v4/letter/j/5fc32e/32.png) [@jdemp0913](https://discuss.elastic.co/u/jdemp0913)\
**Post date:** [May 21, 2019, 9:26pm UTC](https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007/5 "2019-05-21T21:26:30Z")

</div>

Right, I am currently using .Net (NEST) and c# and am having some good initial results. You have confirmed what I have believed to be true. Thanks.

---

<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:** [June 18, 2019, 9:26pm UTC](https://discuss.elastic.co/t/one-to-many-table-relationship-pre-processing/182007/6 "2019-06-18T21:26:33Z")

</div>

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