Are there something like SELECT IF EXISTS...Funktion in Dremio

[Violetta]

Hi,
I have to create Views (Virtual tables) for some collections with complex nested JSON-Documents.
The thing is, some collections have not all properties that I need, but the view must be the same for all.
In this case I need to return “0” at this place.
Or
I have property that can have one of 3 Sub-objects.
Like this :
“lines”:[
{
“line_number”: 1,
{
“item” : {
“number” : “591”,
“quantity” : 1.0,
“fullamount” : 0.49,
“quantity_factor” : 1,
“group_id” : 12,
“price_per_unit” : 0.49,
“base_amounts_per_vat_id” : [
{
“excl_vat” : 5e-005,
“vat” : 0.48995,
“id” : 1,
“incl_vat” : 0.49
}
]
},…
At the place of “base_amount_per_vat_id” can be:
discounts_per_vat_id or extra_amounts_per_vat_id.

I have to return something like this:
line_number | itemamounts |id | incl_vat | excl_vat | vat |

1 | “base_amount” |1 | 0.49 | 5e-005 | 0.48995 |
2 | “discounts_amount” |…

Thanks

[laurent]

Dremio supports both CASE and COALESCE functions, which I think would fit your need.

More details at http://docs.dremio.com/sql-reference/sql-functions/conditional.html

[igreg]

I’m trying to do the same and Dremio shows an error: “Unable to find the referenced field: [product.details.price].” Below is a related thread. Looks like COALESCE and CASE can check if a field is null but not that the field exists. Is there an IF Exists type function in dremio?

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

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: SELEC…

[laurent]

You seem to be already aware of the kvgen experimental function (mentionned in Elasticsearch query - extract all fields from complex data type (JSON)). Currently, I don’t think we have a different way of discovering if a field is present or not.

[igreg]

Thanks @laurent
It would be a useful feature to be able to conditionally select fields if they exist especially for elasticsearch where the filelds are dynamic and might or might not exist within an index.