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


#1

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


#2

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


#3

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?


#4

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.


#5

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.