One missing timeseries metric in DQL query, result in "There are no records" for the whole query

[cabeywardhana]

I have a complex query which is incorporating multiple metrics in one DQL query via multiple joins.

My issue is that if one of the metric(the whole timeseries) is missing, the whole query returns "There are no records"

timeseries avg(metric1)
    interval: 10m
    by: {id}
| join [ 
    timeseries avg(metric2, default:0), nonempty:true,  
    interval: 10m
    by: {id}
], on: {_id}, prefix:"prefix1"
| join [
    timeseries avg(metric3), 
    interval: 10m
    by: {id}
], on: {_id}, prefix:"prefix2"

I have tried adding nonempty and default value to DQL query as suggested here for the missing metric without any luck. e.g. metric2 is missing the timeseries. 

timeseries avg(metric2, default:0), nonempty:true, 

The result I am expecting is that the complex query will still return a result with the metrics data from other two(available) timeseries + default values for the missing timeseries metric. 
Is this possible ?

[krzysztof_hoja · Accepted solution]

Before I attempt to explain, here are my test data. I will use 2 metrics for 2 hosts. First metric is available for both host while second only for one:

If I retrieve both metrics using single timeseries command, the result would look like:

As you can see only one record with pair of metrics is returned for the host where both metrics are available. To have both metrics using such construct, I would use union:true option of timeseries command.

this option cases that records/rows when some metrics are missing are not eliminated. Here is how join command behaves with the same data:

But join command offers also option kind:leftOuter which does not eliminate records returned by basic query where corresponding record from subquery is missing

Of course first query has to provide superset of what subquery returns.

Kris

[cabeywardhana]

@krzysztof_hoja Thank you for the solution, your awesome suggestion works for my scenario.