Query to select an elasticsearch field which is not part of ES mapping

1

I’m querying an elasticsearch index and extracting a field from a JSON tructure (product.details.price) for which there is no corresponding elasticsearch mapping. i.e The field uses elasticsearch dynamic mapping. In addition this field value can also be null. Below is an example:

In elasticsearch:

{
“_index”: “myIndex”,
“_type”: “data”,
“_id”: “myIndex_20180925141725_5524_1_000000070”,
“_score”: 1.0,
“_source”: {
“product”: {
“details”: {
“price”: null
}
}
}
}

Dremio query:

SELECT product.details.price as price FROM ES.myindex

Dremio shows an error: “Unable to find the referenced field: [product.details.price].”

I’ve tried using the a “CASE” statement and a COALESCE function to populate an empty value for the column if the field is null however the same error is thrown in both cases.

eg:
SELECT COALESCE(product.details.price, ‘’) as price FROM ES.myindex

SELECT CASE WHEN product.details.price IS NULL THEN ‘’ ELSE product.details.price END as price FROM ES.myindex

Is there a way create a virtual dataset which includes nullable fields and fields that are not part of elasticsearch mapping?