Date script not working

Cain310 · October 1, 2019, 9:44pm

I am having trouble writing a script to work with my two date fields.

ES/KB version 5.6.16

"doc": {
            "chargebackDate": "2019-09-23 14:51:29",
            "products": [
              {
                "recurringDate": "2019-09-02",
              }
            ],
          }

I am trying to find all documents that have a difference of < 11 days comparing these two Date fields.

Not sure if this is even possible. I don't need the results to be more precise then a day increment.

i've tried .value and .date.dayOfYear but can seem to get it working.

Any help much appreciated.

rjernst · October 5, 2019, 5:05pm

You havne't shown your mappings, but based on the structure of your example doc, you won't be able to get a value for recurringDate in a script because it is (I assume) a nested field. Each element of the products array is indexed as a separate document in Lucene, yet scripts operate on a single document at a time (and not on the internally created nested docs for such a field). In order to use this within a script you would either need to have fully separate documents per "product" here, or make some choice during indexing as to what recurringDate to place in a field directly on the root document.

Cain310 · October 7, 2019, 3:22pm

Hi Ryan,

Thanks so much for the response. Let me see if i Understand you correctly.

When you say "each Element of the products array" does that mean each key value pair in the nested products array is a separate document?

This is what my mapping looks like.

{
  "mappings": {
    "couchbaseDocument": {
      "dynamic_templates": [
        {
          "date_as_string": {
            "match_mapping_type": "date",
            "match_pattern": "regex",
            "match": "^(holdDate|startDate|endDate)$",
            "mapping": {
              "type": "text",
              "fields": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        },
        {
          "ignore": {
            "match_pattern": "regex",
            "match": "^(esQuery|aliasDict|gwData|orderData|firstOrderData|trackData|firstTrackData|dashboardData|jwts|permissions|error|credentials|interval|clvByAffiliate|clvByCountry|clvDistribution|filters)$",
            "mapping": {
              "enabled": false
            }
          }
        }
      ],
      "properties": {
        "doc": {
          "properties": {
            "products": {
              "properties": {
                "recurringDate": {
                  "type": "text",
                  "fields": {
                    "date": {
                      "type": "date",
                      "format": "yyyy-MM-dd",
                      "ignore_malformed": true
                    },
                    "keyword": {
                      "type": "keyword"
                    }
                  }
                }
              }
            },
            "action": {
              "type": "nested",
              "dynamic": true
            },
            "externalId": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword"
                }
              }
            },
            "recurringDate": {
              "type": "text",
              "fields": {
                "date": {
                  "type": "date",
                  "format": "yyyy-MM-dd",
                  "ignore_malformed": true
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            },
            "chargebackDate": {
              "type": "text",
              "fields": {
                "date": {
                  "type": "date",
                  "format": "yyyy-MM-dd HH:mm:ss",
                  "ignore_malformed": true
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            },
            "acquisitionDate": {
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss",
              "ignore_malformed": true,
              "fields": {
                "text": {
                  "type": "text"
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            },
            "timeStamp": {
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss",
              "ignore_malformed": true,
              "fields": {
                "text": {
                  "type": "text"
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            },
            "date": {
              "type": "date",
              "format": "epoch_millis",
              "ignore_malformed": true,
              "fields": {
                "text": {
                  "type": "text"
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            }
          }
        }
      }
    }
  },
  "settings": {
    "index.mapping.total_fields.limit": 10000
  }
}

This is what my 'order' Document, which contains the nested product array looks like. Some value omitted for privacy and char limit per reply .

{
        "_index": "ban.....",
        "_type": "doc",
        "_id": "Order|05dfcb133189ba801ba5663be46c665badd009810195160419e2462d8b26ce06",
        "_score": 1,
        "_source": {
          "meta": {
            "vbuuid": 126392786703854,
            "rev": "4-15c5065849d800000000000002000000",
            "seqno": 1179,
            "cas": 1568667021474267100,
            "revSeqno": 4,
            "lockTime": 0,
            "flags": 33554432,
            "vbucket": 840,
            "expiration": 0,
            "id": "Order|05dfcb133189ba801ba5663be46c665badd009810195160419e2462d8b26ce06"
          },
          "doc": {
            "_type": "Order",
            "createdDate": "2019-09-05T20:52:35.872Z",
            "externalId": "523...",
            "lastUpdate": "2019-09-16T20:50:20.569Z",
            "relatedId": "352...",
            "acquisitionDate": "2019-07-06 11:39:26",
            "affid": "OA",
            "affiliate": "OA",
            "ancestorId": "35....",
            "billingAddressId": "44170d22a65fd0005f9330eff9c1d477afc6d53f24f1d850c4f52417032e08f1",
            "billingCycle": "1",
            "billingFirstName": "cl....",
            "billingLastName": "mo.....",
            "chargebackDate": "",
            "customerId": "df232f3a70b4dd0fbb20bfd5fe55cf5482d5bbf99cf36017e83a5aa8cb5806be",
            "customersTelephone": "617...",
            "declineReason": "DO NOT RETRY - Bank decline",
            "declineSalvageDiscountPercent": "0",
            "emailAddress": "chm...",
            "externalCustomerId": "456..",
            "gatewayDescriptor": "mect...",
            "gatewayId": "198",
            "ipAddress": "108.74.30.252",
            "isBlacklisted": "0",
            "isChargeback": "0",
            "parentId": "352593",
            "recurringDate": "2019-10-05",
            "gift": {},
            "products": [
              {
                "productId": "0e4f3e5398cc97b572c853cedac98671dcfa70cd349bbc8067ffb83f35d2645d",
                "quantity": "1",
                "isRecurring": "0",
                "recurringDate": "2019-10-05",
                "subscriptionId": "0c72a08ec378a861cf5d51f536a021bd",
                "isShippable": "1",
                "isFullRefund": "0",
                "refundAmount": "0.00",
                "onHold": "0"
              }
            ],
            "Notes": {
              "systemNotes": [
                "September 05, 2019 03:00 PM - ...",
                "September 05, 2019 03:00 PM - ..."
              ]
            },
            "sourceId": "6f46986c-85ba-4207-9d16-c61fff638f0e",
            "clientId": "02d41dbf-bec2-4ee9-b6fa-dc87560f8dc4",
            "orderId": "05dfcb133189ba801ba5663be46c665badd009810195160419e2462d8b26ce06",
            "date": 1567695611000,
            "isInitial": false
          }
        }
      }

rjernst · October 7, 2019, 5:14pm

Thanks for posting the mappings; that helps a lot in understanding your setup.

This is not what I expected. As I previously mentioned, I had thought your products field was a nested field (which I recommend you read up on).

Going back to your original problem:

i've tried .value and .date.dayOfYear but can seem to get it working.

I see you have a date multi field under recurringDate. Given your current mapping, all of the values of recurringDate will be indexed into this field, which would cause multiple values to exist.

Can you elaborate on the error or exact behavior you see when attempting to access doc values with doc['products.recurringDate.date'].value? This would give the earliest value stored (doc values are sorted). Note that doc values must be accessed by the full path to the field, so it is important to use the structure noted there.

Cain310 · October 7, 2019, 9:17pm

I see you have a date multi field under recurringDate . Given your current mapping, all of the values of recurringDate will be indexed into this field, which would cause multiple values to exist.

Ok I have two occurrences or recurringDate one in the products object and one in the parent. Although both occurrences always have the same value when in the same Document. Does it matter when scripting in ES/lucene?

I have a multi field for recurringDate for the purposeof querying them in different ways/visualizations.

multiple values to exist.

When you say multiple values are you referring to 'doc.reccuringDate' (Text field), 'doc.recurringDate.keyword' (nonAnalyzed), 'doc.recurringDate.Date' (Date). If so does this skew results when scripting?

Can you elaborate on the error or exact behavior you see when attempting to access doc values

Yes, So its not erroring it's just not filtering correctly and the results don't make sense. I've tried all the methods I could find including the ones you just mentioned but the results don't reveal any pattern to me. Either it gives me 0 OR all the Documents that fulfill term queries but ignored my script. I've tried troubleshooting my script by reducing the conditions do very simple perams but no luck the results are all over the place.

Also yes i reference the full path.

Example of one i tried

POST /ban.../_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "doc.isChargeback": "1"
          }
        },
        {
          "term": {
            "doc._type": "order"
          }
        }
      ],
      "filter": {
        "bool": {
          "must": {
            "script": {
              "script": {
                "source": "doc['doc.timeStamp'].date.dayOfYear - doc['doc.chargebackDate.date'].date.dayOfYear > params.difference",
                "lang": "painless",
                "params": {
                  "difference": 10
                }
              }
            }
          }
        }
      }
    }
  }
}

for example this should I think return me all documents where the fields have a greater then 10 day difference comparatively. And when flipping the < sign should return the opposite. <--- which is my current intended goal at them moment. Instead what I get is all matching terms in documents when .dayOfYear< chargeback... which is fine but the inverse gives me two documents that are way more then two days in difference...

Hope this makes sense lol. If not happy to rephrase.

rjernst · October 7, 2019, 9:48pm

I would try using script fields to determine what values you are subtracting. Do a query that matches a sample of your docs, and add script fields to return the script values you would be using.

Cain310 · October 8, 2019, 8:05pm

Very interesting. I did like you said as seen here to Identify what is really being subtracted.

POST /ban..../_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "doc.sourceId.keyword": "6f46986c-85ba-4207-9d16-c61fff638f0e"
          }
        },
        {
          "term": {
            "doc.billingCycle": "1"
          }
        },
        {
          "term": {
            "doc.isChargeback": "1"
          }
        },
        {
          "term": {
            "doc._type": "order"
          }
        }
      ]
    }
  },
  "script_fields": {
    "test1": {
      "script": {
        "lang": "painless",
        "source": "doc['doc.timeStamp'].date.dayOfYear"
      }
    },
    "test2": {
      "script": {
        "lang": "painless",
        "source": "doc['doc.chargebackDate.date'].date.dayOfYear"
      }
    }
  }
}

The chargebackDate.date value was always 1 which clearly is not correct. So this seems to be the source of my issues.

I then recall I had to adjust my mapping for this field from this

"chargebackDate": {
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss",
              "ignore_malformed": true,
              "fields": {
                "text": {
                  "type": "text"
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            }

to this in order to get the couchbase db connector 4.1 to properly replicate the field without an error.


"chargebackDate": {
              "type": "text",
              "fields": {
                "date": {
                  "type": "date",
                  "format": "yyyy-MM-dd HH:mm:ss",
                  "ignore_malformed": true
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            }

the chargebackDate field can be in this format "2019-08-14 12:42:44" or be empty "".

This is the error the couchbase connector gives me which i thought would be resolved by adding "ignore_malformed": true .

Couchbase connector 4.1 error -

reason=failed to parse [doc.chargebackDate]]]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: ""

Seems we have identified the real issue at hand which is nice. But now how do you set a Date mapping on a field which can be empty as well?

rjernst · October 8, 2019, 8:39pm

The root of the problem is, as you pointed out, you have some documents without a value for the field. In newer versions of Elasticsearch using doc['field'].value when no values exist for field now produces an error. The correct way to check this is by checking the number of values before accessing the value. In the current version of ES this would be done with:

if (doc['field'].size() == 0) {
  ...
}

On 5.6, you may need to use doc['field'].values.size() == 0.

Cain310 · October 9, 2019, 1:25pm

Ok ill get it done . Do you know why the first mapping of "chargebackDate" produces an error when injesting and the second does not?

rjernst · October 10, 2019, 5:17pm

What error are you receiving?

Cain310 · October 11, 2019, 3:28pm

Issue #1

I'm getting the empty string error and all zeros error when I explicitly try to map any date field. Problem is these date fields can sometimes be empty "", or "0000-00-00". not sure how to properly map a field so that it maintains searchability & AGG.

one of my many attempted mapping examples....

PUT ban....
{
  "mappings": {
    "couchbaseDocument": {
      "dynamic_templates": [
        {
        "labels": {
          "match": "holdDate",
          "match_mapping_type": "string",
          "mapping": {
            "type": "date",
            "ignore_malformed": true
          }
        }
      },
      {
        "labels2": {
          "match": "recurringDate",
          "match_mapping_type": "string",
          "mapping": {
            "type": "date",
            "ignore_malformed": true
          }
        }
      },
        {
          "longs_as_strings": {
            "match": "externalId",
            "match_mapping_type": "long",
            "mapping": {
              "fields": {
                "ignore_above": 256,
                "type": "keyword"
              },
              "type": "text"
            }
          }
        },
        {
          "date_as_string": {
            "match": "^(holdDate|recurringDate|startDate|endDate)$",
            "match_mapping_type": "date",
            "match_pattern": "regex",
            "mapping": {
              "fields": {
                "ignore_above": 256,
                "type": "keyword"
              },
              "type": "text"
            }
          }
        }
      ],
      "properties": {
        "doc": {
          "properties": {
            "products": {
              "properties": {
                "recurringDate": {
                  "enabled": false
                }
              }
            },
            "action": {
              "type": "nested",
              "dynamic": true
            }
          }
        },
        "recurringDate": {
                  "enabled": false
                },
        "externalId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword"
            }
          }
        },
        "chargebackDate": {
          "type": "text",
          "fielddata": true,
          "fields": {
            "date": {
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss",
              "ignore_malformed": true
            },
            "keyword": {
              "type": "keyword"
            }
          }
        },
        "acquisitionDate": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss",
          "ignore_malformed": true,
          "fields": {
            "text": {
              "type": "text"
            },
            "keyword": {
              "type": "keyword"
            }
          }
        },
        "timeStamp": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss",
          "ignore_malformed": true,
          "fields": {
            "text": {
              "type": "text"
            },
            "keyword": {
              "type": "keyword"
            }
          }
        },
        "date": {
          "type": "date",
          "format": "epoch_millis",
          "ignore_malformed": true,
          "fields": {
            "text": {
              "type": "text"
            },
            "keyword": {
              "type": "keyword"
            }
          }
        }
      }
    }
  },
  "settings": {
    "index.mapping.total_fields.limit": 10000
  }
}

15:43:31.012 [es-worker-1] WARN c.c.c.e.i.ElasticsearchWriter - Permanent failure to index event MUT:3/25076435448763@171[0-2151]=Order|7620bb3868f29d981c28830db598b4f3ccc0e3e5b5b3f978d230423689dda7c5; status code: BAD_REQUEST ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse [doc.chargebackDate]]]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: ""]];

85497c4b52ec424d48689dcf7d706; status code: BAD_REQUEST ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse [doc.recurringDate]]]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_field_value_exception, reason=Cannot parse "0000-00-00": Value 0 for monthOfYear must be in the range [1,12]]];

I was able to hack it so the ingest errors go away which at first made me think ok it worked yay.

"chargebackDate": {
              "type": "text",
              "fields": {
                "date": {
                  "type": "date",
                  "format": "yyyy-MM-dd HH:mm:ss",
                  "ignore_malformed": true
                },
                "keyword": {
                  "type": "keyword"
                }
              }
            },

But when querying and scripting the subject date fields 0 hits show up. seems to be skipping these explicitly set date fields all together.

I found this here ignore_malformed to support ignoring JSON objects ingested into fields of the wrong type · Issue #12366 · elastic/elasticsearch · GitHub
maybe It needs to be a dynamic field? idk
but didn't work.

rjernst · October 15, 2019, 11:16pm

these date fields can sometimes be empty "", or "0000-00-00"

You'll need to special case those when indexing then. You have ignore_malformed = true, so the value for that will be skipped when indexing, thus making the document not actually have a value for that field.

Cain310 · October 17, 2019, 8:59pm

Hey so trying to get to that point but after some trouble shooting I have no clue why the below is returning the one document regardless if I change the param.difference to greater then 9...errrr

DELETE test_bankable

GET test_bankable/_mapping

GET test_bankable/_search
{
  "query": {
    "match_all": {}
  }
}

PUT test_bankable
{
  "mappings": {
    "doc": {
      "date_detection": false,
      "dynamic_templates": [
        {
          "dates": {
            "match": ".*chargebackDate|acquisitionDate|timeStamp|recurringDate",
            "match_pattern": "regex",
            "mapping": {
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss||dateOptionalTime"
            }
          }
        }
      ]
    }
  }
}

PUT test_bankable/doc/2
{
  "chargebackDate": "2018-08-29 06:41:37"
}



PUT test_bankable/doc/1
{
  "timeStamp": "2018-08-20 06:41:37"
}


POST test_bankable/_search
{
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": {
        "bool": {
          "must": {
            "script": {
              "script": {
                "source": "doc['timeStamp'].date.dayOfYear - doc['chargebackDate'].date.dayOfYear < params.difference",
                "lang"   : "painless",
                "params": {
                  "difference": 10
                }
              }
            }
          }
        }
      }
    }
  }
}

GET test_bankable/_search
{
  "query": {
      "match_all": {}
  },
  "script_fields": {
    "test1": {
      "script": {
        "lang": "painless",
        "source": "doc['timeStamp'].value"
      }
    },
    "test2": {
      "script": {
        "lang": "painless",
        "source": "doc['chargebackDate'].value"
      }
    }
  }
}

Christian_Dahlqvist · October 17, 2019, 9:05pm

I do not think you can compare fields from two different documents in a script as it runs in the context of a single document.

Cain310 · October 18, 2019, 2:40pm

Ok even with that change results of the script are not accurate sooo weird. Must be a synstax or format issue somewhere.

DELETE test_bankable

GET test_bankable/_mapping

GET test_bankable/_search
{
  "query": {
    "match_all": {}
  }
}

PUT test_bankable
{
  "mappings": {
    "doc": {
      "date_detection": false,
      "dynamic_templates": [
        {
          "dates": {
            "match": ".*chargebackDate|acquisitionDate|timeStamp|recurringDate",
            "match_pattern": "regex",
            "mapping": {
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss||dateOptionalTime"
            }
          }
        }
      ]
    }
  }
}

PUT test_bankable/doc/2
{
  "chargebackDate": "2018-08-29 06:41:37",
  "timeStamp": "2018-08-20 06:41:37"
}



PUT test_bankable/doc/1
{
  "timeStamp": "2018-08-25 06:41:37",
  "chargebackDate": "2018-09-27 06:41:37"
}


POST test_bankable/_search
{
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": {
        "bool": {
          "must": {
            "script": {
              "script": {
                "source": "doc['timeStamp'].date.dayOfYear - doc['chargebackDate'].date.dayOfYear < params.difference",
                "lang"   : "painless",
                "params": {
                  "difference": 9
                }
              }
            }
          }
        }
      }
    }
  }
}

GET test_bankable/_search
{
  "query": {
      "match_all": {}
  },
  "script_fields": {
    "test1": {
      "script": {
        "lang": "painless",
        "source": "doc['timeStamp'].date.dayOfYear"
      }
    },
    "test2": {
      "script": {
        "lang": "painless",
        "source": "doc['chargebackDate'].date.dayOfYear"
      }
    }
  }
}

Cain310 · October 18, 2019, 2:46pm

aha subtracting a smaller number from a greater yields a negative. MATHS!!!!

system · November 15, 2019, 5:05pm

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

Topic		Replies	Views
Lucene expressions script - date comparison, boolean Elasticsearch	1	963	February 17, 2017
Comparing Date Fields of Nested Objects Elasticsearch painless	2	577	August 6, 2021
Script_fields calcul in nested Elasticsearch	2	670	April 17, 2019
Impossible to use scripts in nested filters? Elasticsearch	3	473	June 1, 2017
Doc Script Fields - Date Fields outside of index Elasticsearch	2	622	July 5, 2017

Date script not working

Related topics