How to aggregate all similar FACET'd endpoints

As seen in the image attached, when I facet the endpoints for the line graph, I get several results that contain unnecessary subqueries. According to the image, I would want all of Patient endpoints grouped together and Condition endpoints grouped together, and these new aggregated results should be reflected on the graph.

It should look like the following: ‘/fhir/v4/resources/Patient’ and ‘/fhir/v4/resources/Condition’

This is the query that I currently have:
SELECT count(*) from Transaction where appName like ‘fhir-API’ FACET request.uri TIMESERIES SINCE 1 day ago EXTRAPOLATE

Hi, @Gwiggins: You might take a look at FACET CASES:

SELECT count(*) 
FROM Transaction 
WHERE appName like 'fhir-API' 
FACET CASES (
  WHERE request.uri LIKE '%/resources/Patient/%' AS 'Patients', 
  WHERE request.uri LIKE '%/resources/Condition/%' AS 'Conditions'
)

For some reason when I try using FACET CASES, it successfully consolidates into 1 line, but it does not numerically add up all of its similar endpoints.

For example, there are 3 different patient results reflected on my graph which contain ~300 transactions when added together. However, when using the cases it does consolidate into 1 Patients line, but I only see 10 transactions reflected on the graph which is a reflection of just 1/3 patient results.

Try adding LIMIT MAX to the query.

I think the issue is actually that when doing the FACET CASES, WHERE request.uri LIKE ‘%/resources/Patient/%’ AS ‘Patients’, captures all the endpoints with subqueries but leaves out the endpoints without subqueries such as ‘/fhir/v4/Patient’.

Is there a way that I can do a FACET CASE that can combine WHERE request.uri LIKE ‘%/resources/Patient/%’ AS ‘Patients’ AND WHERE request.uri LIKE ‘%/resources/Patient%’ AS 'Patients’

My hope is to combine the ‘Patient/%’ and ‘Patient%’ data into the same variable name ‘Patients’

Just use the second WHERE clause, without the trailing slash:

WHERE request.uri LIKE '%/resources/Patient%' AS 'Patients'

Should capture both cases.

1 Like