No field found for [nextDueDate] in mapping with types []

Plugins SQL
1

Using the SQL query:

SELECT *
from dev_entity_policy
where date(nextDueDate) <= curdate()

I get back the error:

"caused_by" : {
        "type" : "illegal_argument_exception",
        "reason" : "No field found for [nextDueDate] in mapping with types []"
    }

I know that nextDueDate is a field in each doc of the index that contains a datetime (or is it timestamp? – is there a way to get the datatype for a given field?).

Any suggestions on how to properly do an inequality query properly?

2

@greg.fenton You can check your index mapping by Get index - OpenSearch documentation. Thanks!

3

Hi @daichen !

Are you suggesting that I ensure the nextDueDate is defined in the index? Am I supposed to declare the field as being a date field?

(Sorry for the newbie questions…I inherited someone else’s project and I’m pretty new to OpenSearch)

4

@greg.fenton No worries! I was suggesting we check the index mapping first by GET [index]/_mapping API. Then you will see if there is nextDueDate and its type. If everything looks good, please share your steps that we can reproduce, including test index mapping, some data, each request you ran. Because the error message you posted seems not from SQL plugin, it would helpful if you can provide more context. Thanks!

2 Likes
5

Sorry for the very tardy reply.

I rebuilt the index and that error was resolved. Seems our first document to include nextDueDate did not have a valid date. We are using Dynamic Mapping, so things got confused.