# How to Facet with a "Non-Nested field as Key-Field" and a "Nested filed as Value-Field"

**URL:** <https://discuss.elastic.co/t/how-to-facet-with-a-non-nested-field-as-key-field-and-a-nested-filed-as-value-field/8978>\
**Category:** Elasticsearch\
**Created:** [September 10, 2012, 10:15pm UTC](https://discuss.elastic.co/t/how-to-facet-with-a-non-nested-field-as-key-field-and-a-nested-filed-as-value-field/8978 "2012-09-10T22:15:19Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pradeep\_2](https://avatars.discourse-cdn.com/v4/letter/p/9de053/32.png) [@Pradeep\_2](https://discuss.elastic.co/u/Pradeep_2)\
**Post date:** [September 10, 2012, 10:15pm UTC](https://discuss.elastic.co/t/how-to-facet-with-a-non-nested-field-as-key-field-and-a-nested-filed-as-value-field/8978/1 "2012-09-10T22:15:19Z")

</div>

Can we do nested facet with a non-nested key-field?

My doc has this structure.. ( each eid has many payment records)

{  
"eid": {  
"type": "long"  
},  
"payments": {  
"type": "nested",  
"pay": {  
"type": "double"  
},  
"pay": {  
"type": "long"  
}  
}  
}

Now I can do a facet and terms stats as below...

which is like ..(sql way) get sum(pay) group by year

"facets": {  
"sal\_stats": {  
"terms\_stats": {  
"key\_field": ["year"],  
"value\_field": ["pay"],  
"size": 0  
}  
"nested": "payments"  
}

Now how can I do.. get sum(pay) group by eid , without adding eid to each  
payment record ?

Appreciate any help..

--

---

<div class="post-metadata">

**Author:** ![Pradeep\_2](https://avatars.discourse-cdn.com/v4/letter/p/9de053/32.png) [@Pradeep\_2](https://discuss.elastic.co/u/Pradeep_2)\
**Post date:** [September 13, 2012, 8:37pm UTC](https://discuss.elastic.co/t/how-to-facet-with-a-non-nested-field-as-key-field-and-a-nested-filed-as-value-field/8978/2 "2012-09-13T20:37:14Z")

</div>

gentle bounce.. Please help guys.. Am unable to figure this out.. THanks.

On Monday, September 10, 2012 6:15:19 PM UTC-4, Pradeep wrote:

> Can we do nested facet with a non-nested key-field?
> 
> My doc has this structure.. ( each eid has many payment records)
> 
> {  
> "eid": {  
> "type": "long"  
> },  
> "payments": {  
> "type": "nested",  
> "pay": {  
> "type": "double"  
> },  
> "pay": {  
> "type": "long"  
> }  
> }  
> }
> 
> Now I can do a facet and terms stats as below...
> 
> which is like ..(sql way) get sum(pay) group by year
> 
> "facets": {  
> "sal\_stats": {  
> "terms\_stats": {  
> "key\_field": ["year"],  
> "value\_field": ["pay"],  
> "size": 0  
> }  
> "nested": "payments"  
> }
> 
> Now how can I do.. get sum(pay) group by eid , without adding eid to each  
> payment record ?
> 
> Appreciate any help..

--

---

<div class="post-metadata">

**Author:** ![Rauan\_Maemirov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rauan_maemirov/32/826_2.png) [@Rauan\_Maemirov](https://discuss.elastic.co/u/Rauan_Maemirov)\
**Post date:** [September 14, 2012, 6:29am UTC](https://discuss.elastic.co/t/how-to-facet-with-a-non-nested-field-as-key-field-and-a-nested-filed-as-value-field/8978/3 "2012-09-14T06:29:26Z")

</div>

I had a similar task and I guess I somehow managed it to work like this:

"facets": {  
"sal\_stats": {  
"terms\_stats": {  
"key\_field": "year",  
"value\_field": "payments.pay"  
}  
"nested": "payments"  
}

Here I suppose, that year and payments are neighbor fields in doc.

I'm not sure if it's possible to create mapping with the same name for two  
different value types, i.e nested field 'pay' (not to mention that they're  
not covered within 'properties', but I assume this is just an example).

Le mardi 11 septembre 2012 04:15:19 UTC+6, Pradeep a écrit :

> Can we do nested facet with a non-nested key-field?
> 
> My doc has this structure.. ( each eid has many payment records)
> 
> {  
> "eid": {  
> "type": "long"  
> },  
> "payments": {  
> "type": "nested",  
> "pay": {  
> "type": "double"  
> },  
> "pay": {  
> "type": "long"  
> }  
> }  
> }
> 
> Now I can do a facet and terms stats as below...
> 
> which is like ..(sql way) get sum(pay) group by year
> 
> "facets": {  
> "sal\_stats": {  
> "terms\_stats": {  
> "key\_field": ["year"],  
> "value\_field": ["pay"],  
> "size": 0  
> }  
> "nested": "payments"  
> }
> 
> Now how can I do.. get sum(pay) group by eid , without adding eid to each  
> payment record ?
> 
> Appreciate any help..

--

---

<div class="post-metadata">

**Author:** ![Pradeep\_2](https://avatars.discourse-cdn.com/v4/letter/p/9de053/32.png) [@Pradeep\_2](https://discuss.elastic.co/u/Pradeep_2)\
**Post date:** [September 14, 2012, 1:56pm UTC](https://discuss.elastic.co/t/how-to-facet-with-a-non-nested-field-as-key-field-and-a-nested-filed-as-value-field/8978/4 "2012-09-14T13:56:40Z")

</div>

I am afraid, that doesn't work.. That appears to be the intuitive way. I  
tried that before.. that's when I figured it wouldn't work.

The response is like

_facets": {_

- 

```
   "sal_stats": {*

```

- 

```
       "_type": "terms_stats",*

```

- 

```
       "missing": 196462,*

```

- 

```
       "terms": []*

```

- 

```
   },....

```

- 

I guess, it looks for the fields inside nested payment object, and at the  
document level. So it reports everything as missing.

On Friday, September 14, 2012 2:29:26 AM UTC-4, Rauan Maemirov wrote:

> I had a similar task and I guess I somehow managed it to work like this:
> 
> "facets": {  
> "sal\_stats": {  
> "terms\_stats": {  
> "key\_field": "year",  
> "value\_field": "payments.pay"  
> }  
> "nested": "payments"  
> }
> 
> Here I suppose, that year and payments are neighbor fields in doc.
> 
> I'm not sure if it's possible to create mapping with the same name for two  
> different value types, i.e nested field 'pay' (not to mention that they're  
> not covered within 'properties', but I assume this is just an example).
> 
> Le mardi 11 septembre 2012 04:15:19 UTC+6, Pradeep a écrit :
> 
> > Can we do nested facet with a non-nested key-field?
> > 
> > My doc has this structure.. ( each eid has many payment records)
> > 
> > {  
> > "eid": {  
> > "type": "long"  
> > },  
> > "payments": {  
> > "type": "nested",  
> > "pay": {  
> > "type": "double"  
> > },  
> > "pay": {  
> > "type": "long"  
> > }  
> > }  
> > }
> > 
> > Now I can do a facet and terms stats as below...
> > 
> > which is like ..(sql way) get sum(pay) group by year
> > 
> > "facets": {  
> > "sal\_stats": {  
> > "terms\_stats": {  
> > "key\_field": ["year"],  
> > "value\_field": ["pay"],  
> > "size": 0  
> > }  
> > "nested": "payments"  
> > }
> > 
> > Now how can I do.. get sum(pay) group by eid , without adding eid to  
> > each payment record ?
> > 
> > Appreciate any help..

--

---

<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, 3:12am UTC](https://discuss.elastic.co/t/how-to-facet-with-a-non-nested-field-as-key-field-and-a-nested-filed-as-value-field/8978/5 "2017-07-06T03:12:44Z")

</div>

Closed on July 6, 2017, 3:12am UTC