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:
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.
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?