Script Field with Nested Objects

Elastic Stack Elasticsearch
1

Hi -- I am hoping someone can help me (or let me know that ES doesn't do this so I can stop trying!). We're using ES 7.7.0.

I have data where there are multiple items within an object -- as an example, each document represents a shopping cart and the 'order' object has an arbitrary number of 'item#' objects within it. Each 'item#' has two numeric values that I want to manipulate with a script field -- in this example, for each 'item#', multiply the 'count' and 'price' values to get the line-item total for a receipt.

Example record:

PUT /calctest-20220614/_doc/3
{
   "order": { "item1": { "cost": 31.55,"count": 111 },"item2": { "cost": 62.55,"count": 222 },"item3": { "cost": 93.55,"count": 333 } }
}

I have a search where I can handle a known number of elements and generate script_field line-item totals.

POST /calctest-20220614/_search
{
  "query"  : { "match_all" : {} },
	"_source": ["order.item*.item", "order.item*.count", "order.item*.cost"],
  "script_fields" : {
    "total_cost_1" : {
      "script" : 
      {
        "lang": "painless",
        "source": "return doc['order.item1.cost'].value * doc['order.item1.count'].value;"
      }
    },
    "total_cost_2" : {
      "script" : 
      {
        "lang": "painless",
        "source": "return doc['order.item2.cost'].value * doc['order.item2.count'].value;"
      }
    },
    "total_cost_3" : {
      "script" : 
      {
        "lang": "painless",
        "source": "return doc['order.item3.cost'].value * doc['order.item3.count'].value;"
      }
    }    
  }
}

I know that I can test to see if elements are present, so hypothetically I could brute force it and, say, have 1000 script fields and create some front-end restriction that prevents orders exceeding 1000 items. What I'd really like to be able to do is iterate over the items returned by my search and perform the calculation for each item.

2

Hi @LisaJ !

I found your mapping cumbersome for this iteration you need to do.
I would change the field mapping to something like this:

{
  "order": [
    {
      "name": "item1",
      "cost": 31.55,
      "count": 111
    },
    {
      "name": "item2",
      "cost": 62.55,
      "count": 222
    },
    {
      "name": "item3",
      "cost": 93.55,
      "count": 333
    }
  ]
}

Then I would use an aggregation to calculate the "total_cost".
It is also good to monitor the performance of the query because it is using script and aggregations.

3

Thanks for the suggestion -- I was hoping we'd be able to iterate through and perform computations on each of the nested objects. Manipulating the data as it is ingested (input comes from a vendor platform, so I cannot just tell it to format the data more reasonably!) or using some external scripting to parse the stored data is probably the direction we'll go.

4

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