# Aggregation based on foreign key

**URL:** <https://discuss.elastic.co/t/aggregation-based-on-foreign-key/49026>\
**Category:** Elasticsearch\
**Created:** [May 3, 2016, 8:48am UTC](https://discuss.elastic.co/t/aggregation-based-on-foreign-key/49026 "2016-05-03T08:48:14Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![fred-d](https://avatars.discourse-cdn.com/v4/letter/f/cc9497/32.png) [@fred-d](https://discuss.elastic.co/u/fred-d)\
**Post date:** [May 3, 2016, 8:48am UTC](https://discuss.elastic.co/t/aggregation-based-on-foreign-key/49026/1 "2016-05-03T08:48:15Z")

</div>

Hello  
I am new to Elasticsearch and encountered some difficulties with the mapping and transfer of my SQL-data. I hope you can help.

The orginal data has a foreign key that can't be directly used in elasticsearch (AFAIK).

```
"mappings" : {
	"my_type" : {
		"properties" : {
			"pk" : {"type": "integer" },
			"fk" : {"type": "integer" },
			"a" : { "type" : "string" },
			"b" : { "type" : "string" },
			...
		}
	}
}

```

For an aggregation, I want to put documents in a bucket (e.g. based on values of "a") and then add all documents whose value for "fk" corresponds to the "pk" of a document already in the bucket.

Denormalization would be problematic since there are many fields.  
Nesting is problematic because there might be several layers.

---

<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 5, 2017, 10:54pm UTC](https://discuss.elastic.co/t/aggregation-based-on-foreign-key/49026/2 "2017-07-05T22:54:27Z")

</div>

Closed on July 5, 2017, 10:54pm UTC