# Aggregate combinations of nested documents

**URL:** <https://discuss.elastic.co/t/aggregate-combinations-of-nested-documents/278796>\
**Category:** Elasticsearch\
**Tags:** painless\
**Created:** [July 15, 2021, 1:52pm UTC](https://discuss.elastic.co/t/aggregate-combinations-of-nested-documents/278796 "2021-07-15T13:52:49Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andy\_Gout](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andy_gout/32/91680_2.png) [@Andy\_Gout](https://discuss.elastic.co/u/Andy_Gout)\
**Post date:** [July 15, 2021, 1:52pm UTC](https://discuss.elastic.co/t/aggregate-combinations-of-nested-documents/278796/1 "2021-07-15T13:52:49Z")

</div>

Using Elasticsearch, I would like to aggregate combinations of nested documents.

Take a hypothetical index of movie data with these mappings:

```auto
{
	mappings: {
		properties: {
			title: {
				type: 'keyword'
			},
			people: {
				type: 'nested',
				properties: {
					id: {
						type: 'keyword'
					},
					name: {
						type: 'keyword'
					},
					role: {
						type: 'keyword'
					}
				}
			}
		}
	}
}

```

And these docs:

```auto
{
	title: "Goodfellas",
	people: [
		{ id: '101', name: "Martin Scorsese", role: "Director" },
		{ id: '102', name: "Robert De Niro", role: "Actor" },
		{ id: '103', name: "Ray Liotta", role: "Actor" },
		{ id: '104', name: "Joe Pesci", role: "Actor" },
		{ id: '105', name: "Frank Vincent", role: "Actor" }
	]
},
{
	title: "Cape Fear",
	people: [
		{ id: '101', name: "Martin Scorsese", role: "Director" },
		{ id: '102', name: "Robert De Niro", role: "Actor" },
		{ id: '106', name: "Nick Nolte", role: "Actor" },
		{ id: '107', name: "Jessica Lange", role: "Actor" }
	]
},
{
	title: "Casino",
	people: [
		{ id: '101', name: "Martin Scorsese", role: "Director" },
		{ id: '102', name: "Robert De Niro", role: "Actor" },
		{ id: '108', name: "Sharon Stone", role: "Actor" },
		{ id: '104', name: "Joe Pesci", role: "Actor" },
		{ id: '105', name: "Frank Vincent", role: "Actor" }
	]
},
{
	title: "Heat",
	people: [
		{ id: '109', name: "Michael Mann", role: "Director" },
		{ id: '110', name: "Al Pacino", role: "Actor" },
		{ id: '102', name: "Robert De Niro", role: "Actor" },
		{ id: '111', name: "Val Kilmer", role: "Actor" }
	]
}
{
	title: "The Irishman",
	people: [
		{ id: '101', name: "Martin Scorsese", role: "Director" },
		{ id: '102', name: "Robert De Niro", role: "Actor" },
		{ id: '110', name: "Al Pacino", role: "Actor" },
		{ id: '104', name: "Joe Pesci", role: "Actor" }
	]
}

```

Is there a way of aggregating pairs of people without having a specific person as a fixed starting point? E.g.

- Martin Scorsese and Robert De Niro: 4
- Martin Scorsese and Joe Pesci: 3
- Robert De Niro and Joe Pesci: 3
- Robert De Niro and Al Pacino: 2
- Martin Scorsese and Ray Liotta: 1
- …

I would also like to:

Specify Director-Actor pairs only, e.g.

- Martin Scorsese and Robert De Niro: 4
- Martin Scorsese and Joe Pesci: 3
- Martin Scorsese and Ray Liotta: 1
- Martin Scorsese and Nick Nolte: 1
- Michael Mann and Robert De Niro: 1
- …

Increase the pairs to triples, quadruples, etc., e.g. triples:

- Martin Scorsese and Robert De Niro and Joe Pesci: 3
- Martin Scorsese and Robert De Niro and Frank Vincent: 2
- Martin Scorsese and Robert De Niro and Ray Liotta: 1
- Martin Scorsese and Ray Liotta and Joe Pesci: 2
- Robert De Niro and Ray Liotta and Frank Vincent: 2
- …

Include the derivation of the combinations (which would perhaps require a multi-level aggregation), e.g.

- Martin Scorsese and Robert De Niro: 4 (Goodfellas, Cape Fear, Casino, The Irishman)
- Martin Scorsese and Joe Pesci: 3 (Goodfellas, Casino, The Irishman)
- Robert De Niro and Joe Pesci: 3 (Goodfellas, Casino, The Irishman)
- Robert De Niro and Al Pacino: 2 (Heat, The Irishman)
- Martin Scorsese and Ray Liotta: (Goodfellas)

The potential solutions I can think of are:

- Calculate the pairs prior to indexing the document and include it as a property that can be used as the term on which to aggregate, e.g. a set of `compoundId` values that for Goodfellas would be: `101-102`, `101-103`, `101-104`, `102-103`, `102-104`, `103-104` (though there would need to be some subsequent logic to acquire the corresponding names for the people represented by these IDs).
- Write a Painless script that can calculate the pairs at query time, though given the numerous people combinations that each document could have and repeating that for a large amount of data (let's say ~1m documents) it's easily possible that such a query would struggle and not be practical for repeated usage in a live application.

Ideally I'd like to be able to produce these results using a single Elasticsearch aggregation, although appreciate this may not be possible.

What solutions are there to this problem?

Thanks in advance.

---

<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:** [August 12, 2021, 1:53pm UTC](https://discuss.elastic.co/t/aggregate-combinations-of-nested-documents/278796/2 "2021-08-12T13:53:17Z")

</div>

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