Scripted Field in Kibana

hiba · October 14, 2020, 11:50am

Hi all,

i want to calculate the capacity of every room using scripted field:

my data looks ike this:
rooms
{
"board": "Soft All Inclusive",
"quantity": 1,
"roomId": 42114,
"paxes": {
"infant": 1,
"adults": 2,
"children": 0
},
"room": "Standard Double Standard Vue Jardin"
},
{
"board": "Soft All Inclusive",
"quantity": 1,
"roomId": 42115,
"paxes": {
"infant": 1,
"adults": 2,
"ages": "[6]",
"children": 1
},
"room": "Standard Triple Standard Vue Jardin"
},
{
"board": "Soft All Inclusive",
"quantity": 1,
"roomId": 42115,
"paxes": {
"infant": 0,
"adults": 2,
"ages": "[9]",
"children": 1
},
"room": "Standard Triple Standard Vue Jardin"
},
{
"board": "Soft All Inclusive",
"quantity": 1,
"roomId": 42115,
"paxes": {
"infant": 0,
"adults": 2,
"ages": "[5]",
"children": 1
},
"room": "Standard Triple Standard Vue Jardin"
}

the capacity of a room is the sum of "rooms.paxes.children" and "rooms.paxes.adults".

So,i create a scripted field "capacityRoom" with number type :

(doc['rooms.paxes.children'].sum() + doc['rooms.paxes.adults'].sum())

the problem with this script is that return capacityRoom = 11
whene it must return :
capacityRoom = 2 (for the room with the ID = 42114)
and
capacityRoom = 3 (for the room with the ID = 42115)

any help please!

ylasri · October 14, 2020, 12:01pm

Why (doc['rooms.paxes.children'].sum() + doc['rooms.paxes.adults'].sum()) ?
and not simply (doc['rooms.paxes.children'].value + doc['rooms.paxes.adults'].value)

hiba · October 14, 2020, 12:39pm

Hi @ylasri ,

i try this script : (doc['rooms.paxes.children'].value + doc['rooms.paxes.adults'].value) .
but i got this error in dicover :

bug

ylasri · October 14, 2020, 12:51pm

Can you share your index mapping and the full error ?

I tried with your examples as follow

PUT my-rooms
{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0
  },
  "mappings": {
    "properties": {
      "board": {
        "type": "keyword"
      },
      "room": {
        "type": "keyword"
      },
      "quantity": {
        "type": "integer"
      },
      "roomId": {
        "type": "keyword"
      },
      "paxes": {
        "properties": {
          "infant": {
            "type": "integer"
          },
          "adults": {
            "type": "integer"
          },
          "children": {
            "type": "integer"
          },
          "ages": {
            "type": "keyword"
          }
        }
      }
    }
  }
}


POST my-rooms/_doc
{
  "board": "Soft All Inclusive",
  "quantity": 1,
  "roomId": 42114,
  "paxes": {
    "infant": 1,
    "adults": 2,
    "children": 0
  },
  "room": "Standard Double Standard Vue Jardin"
}


POST my-rooms/_doc
{
  "board": "Soft All Inclusive",
  "quantity": 1,
  "roomId": 42115,
  "paxes": {
    "infant": 1,
    "adults": 2,
    "ages": "[6]",
    "children": 1
  },
  "room": "Standard Triple Standard Vue Jardin"
}

POST my-rooms/_doc
{
  "board": "Soft All Inclusive",
  "quantity": 1,
  "roomId": 42115,
  "paxes": {
    "infant": 0,
    "adults": 2,
    "ages": "[9]",
    "children": 1
  },
  "room": "Standard Triple Standard Vue Jardin"
}

POST my-rooms/_doc
{
  "board": "Soft All Inclusive",
  "quantity": 1,
  "roomId": 42115,
  "paxes": {
    "infant": 0,
    "adults": 2,
    "ages": "[5]",
    "children": 1
  },
  "room": "Standard Triple Standard Vue Jardin"
}


GET my-rooms/_search


GET my-rooms/_mapping

hiba · October 14, 2020, 1:04pm

Blockquote "rooms" : {
"properties" : {
"board" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"paxes" : {
"properties" : {
"adults" : {
"type" : "long"
},
"ages" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"children" : {
"type" : "long"
},
"infant" : {
"type" : "long"
}
}
},
"quantity" : {
"type" : "long"
},
"room" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"roomId" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}

hiba · October 14, 2020, 1:06pm

my index Mapping:

{
"booking" : {
"mappings" : {
"properties" : {
"@timestamp" : {
"type" : "date"
},
"@version" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"arrDate" : {
"type" : "date"
},
"bookingId" : {
"type" : "long"
},
"bookingRef" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"bookings" : {
"properties" : {
"statusId" : {
"type" : "long"
}
}
},
"cancelDeadline" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss"
},
"channel" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"city" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"confirmedDate" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss"
},
"createdDate" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss"
},
"currency" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"customerId" : {
"type" : "long"
},
"depDate" : {
"type" : "date"
},
"email" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"firstName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"hotelAddress" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"hotelCity" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"hotelCountry" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"hotelId" : {
"type" : "long"
},
"hotelName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"http_request_failure" : {
"properties" : {
"error" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"name" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"request" : {
"properties" : {
"method" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"url" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"runtime_seconds" : {
"type" : "float"
}
}
},
"ip" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"isXML" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"lastName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"mobile" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"name" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"options" : {
"properties" : {
"adults" : {
"type" : "long"
},
"children" : {
"type" : "long"
},
"description" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"designation" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"price" : {
"type" : "float"
},
"productType" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"productTypeId" : {
"type" : "long"
},
"purchasePrice" : {
"type" : "float"
}
}
},
"partnerId" : {
"type" : "long"
},
"partnerName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"paxes" : {
"properties" : {
"age" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"firstName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"lastName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"paxType" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"title" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"price" : {
"type" : "float"
},
"purchasePrice" : {
"type" : "float"
},
"rooms" : {
"properties" : {
"board" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"paxes" : {
"properties" : {
"adults" : {
"type" : "long"
},
"ages" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"children" : {
"type" : "long"
},
"infant" : {
"type" : "long"
}
}
},
"quantity" : {
"type" : "long"
},
"room" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"roomId" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"status" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"supplierId" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"supplierName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"tags" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"title" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
}

ylasri · October 14, 2020, 1:08pm

Sounds working on preview, can you share the full error you got on Discover ?

hiba · October 14, 2020, 1:11pm

search_phase_execution_exception
all shards failed

Error: Bad Request
at Fetch._callee3$ (http://localhost:5601/33813/bundles/core/core.entry.js:34:109213)
at l (http://localhost:5601/33813/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:368:155323)
at Generator._invoke (http://localhost:5601/33813/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:368:155076)
at Generator.forEach.e. [as next] (http://localhost:5601/33813/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:368:155680)
at fetch_asyncGeneratorStep (http://localhost:5601/33813/bundles/core/core.entry.js:34:102354)
at _next (http://localhost:5601/33813/bundles/core/core.entry.js:34:102670)

ylasri · October 14, 2020, 1:13pm

Can you try this query on your Kibana Dev Console

GET my-rooms/_search
{
    "script_fields": {
    "capacityRoom": {
      "script": {
        "source": "doc['rooms.paxes.children'].value + doc['rooms.paxes.adults'].value",
        "lang": "painless"
      }
    }
  },
  "query": {
    "match_all": {}
  }
}

hiba · October 14, 2020, 1:16pm

it works :

{
"took" : 0,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560029",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
1
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560480",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
3
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560415",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
2
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560317",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
3
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560282",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
1
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560233",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
2
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560182",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
3
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560176",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
1
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560175",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
3
]
}
},
{
"_index" : "booking",
"_type" : "_doc",
"_id" : "560156",
"_score" : 1.0,
"fields" : {
"capacityRoom" : [
4
]
}
}
]
}
}

ylasri · October 14, 2020, 1:20pm

Ok, means that there are some documents that does not contains fields to compute capacity

ylasri · October 14, 2020, 1:22pm

Add conditions to your scripted field like this and try again

if( !doc['rooms.paxes.children'].empty && !doc['rooms.paxes.adults'].empty) {
    return doc['rooms.paxes.children'].value + doc['rooms.paxes.adults'].value;
} else return null;

hiba · October 14, 2020, 1:26pm

thanks a lot @ylasri
it works fine .

dpr · October 15, 2020, 5:58am

One downside of this script is that it will only return a value for documents that have both children and adults, and will return a null if the parent document is missing either.

To better handle documents that have one empty value and one present value:

return (doc['rooms.paxes.children'].empty ? 0 : doc['rooms.paxes.children'].value) + 
       (doc['rooms.paxes.adults'].empty ? 0 : doc['rooms.paxes.adults'].value);

Possible downside of this approach: it returns a numeric "0" if the source document is missing both children and adults. (instead of just null).

system · November 12, 2020, 5:58am

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