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!