# Scripted field: Joining a groupped value into indice

**URL:** <https://discuss.elastic.co/t/scripted-field-joining-a-groupped-value-into-indice/222898>\
**Category:** Kibana\
**Created:** [March 10, 2020, 9:42am UTC](https://discuss.elastic.co/t/scripted-field-joining-a-groupped-value-into-indice/222898 "2020-03-10T09:42:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![KTH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kth/32/49908_2.png) [@KTH](https://discuss.elastic.co/u/KTH)\
**Post date:** [March 10, 2020, 9:42am UTC](https://discuss.elastic.co/t/scripted-field-joining-a-groupped-value-into-indice/222898/1 "2020-03-10T09:42:37Z")

</div>

Hi.

I have a OrderItem-indice in Elasticsearch cluster, where this indice contains information on each ordered item. This indice has order timestamp, which I use for building up bar chart all ordered items count visualization by each month. In each month, I would like to split the single bar to 2 parts, single item order count and multiple item order count. This can be done, if only I have a IsMultipleItemOrder boolean property. I don't have this property in the indice, but I have order\_id in each row. If this order\_id is same in multiple rows, these belong to one same order.

In MS SQL I can join the groupped values into existing table like below, I would like to know if I can archieve similar approach in Scripted Field?

```
SELECT * 
FROM OrderItem OI
LEFT JOIN (
                    SELECT CASE WHEN count(OrderId) > 1 THEN 'MultipleItemOrder' ELSE 'SingleItemOrder' END AS 'IsMultipleItem', OrderId
  FROM OrderItem
  group by OrderId) AS MI
  ON MI.OrderId = OI.OrderId

```

Thanks in advance!  
Best regards,  
KTH

---

<div class="post-metadata">

**Author:** ![Marius\_Dragomir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marius_dragomir/32/42087_2.png) [@Marius\_Dragomir](https://discuss.elastic.co/u/Marius_Dragomir)\
**Post date:** [March 12, 2020, 3:51pm UTC](https://discuss.elastic.co/t/scripted-field-joining-a-groupped-value-into-indice/222898/2 "2020-03-12T15:51:14Z")

</div>

In Elasticsearch's data format, the scripted field is only valid in the context of a single document (that translates to row in SQL).  
The format that works best for Elasticsearch would be something like this:  
each document represents an OrderID where you could have multiple items.  
Something like:  
{  
order\_id: 1111  
product\_name: Test  
nr\_of\_items: 5  
}  
So in your data format in SQL, the Item is the object, while in Elasticsearch, the order is the object.

---

<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:** [April 9, 2020, 3:58pm UTC](https://discuss.elastic.co/t/scripted-field-joining-a-groupped-value-into-indice/222898/3 "2020-04-09T15:58:04Z")

</div>

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