Scripted field: Joining a groupped value into indice

[KTH (KT)]

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

[Marius_Dragomir]

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.