Stream: implementers
Topic: GROUP BY in FHIR search
Muhammad Abubakar Ikram (May 30 2020 at 13:44):
How to achieve GROUP BY functionality in FHIR search? for example, I have a use case "Get only the latest issued DiagnosticReport resources for all the Patient resources" how can we search with FHIR in such way?
Michele Mottini (May 30 2020 at 13:53):
In general you cannot do that. There is the $lastn operation for Observation that does something like that, but it is not widely implemented
Muhammad Abubakar Ikram (May 31 2020 at 15:38):
Yes I know about $lastn.
Is there any workaround how could I achieve that?
Muhammad Abubakar Ikram (Jun 03 2020 at 09:23):
I am thinking to implement this feature this way in my server
/fhir/DiagnosticReport/_search?group-by=subject&select=max:issued
anyone got a better idea?
Josh Mandel (Jun 03 2020 at 16:30):
This goes a bit beyond what we've tried to do with the FHIR REST API (and _filter too, for that matter). It's a pretty natural thing to want to express though. I wonder if there's interest in standardizing something like this. (I suspect no.)
Muhammad Abubakar Ikram (Jun 04 2020 at 06:35):
I have seen an answer on StackOverflow related to my case
https://stackoverflow.com/questions/50914101/group-by-like-function-in-fhir-search
there is a search query uri in the answer
{{baseurl}}/Observation?device:Device=123&_sort=-_lastUpdated&_count=1
-
is this -> "_sort=-_lastUpdated" part of the search is standard? I have not found in docs though
-
is _lastUpdated find only the most recently changed resources or also the most recently created resources?
-
if "_sort=-_lastUpdated" is a standard and _lastUpdated also gets the most recently created resource,
then {{baseurl}}/DiagnosticReport/_search?code=12345&_sort=-_lastUpdated, will find the most recently changed and created resources of code type 12345?
Igor Sirkovich (Jun 04 2020 at 07:08):
_lastUpdated is one of the search parameters that apply to all resources, assuming a server you are using supports this. Have a look at https://www.hl7.org/fhir/search.html. If a server supports _lastUpdated, it will have a creation date for a new resource.
Muhammad Abubakar Ikram (Jun 04 2020 at 07:14):
I see, okay so _lastUpdated only works on Resource.meta.lastUpdated.
Amrit (Jun 04 2020 at 11:24):
Is your question about GROUP BY or finding the latest resource? It is not obvious to me that the two are the same.
Michele Mottini (Jun 04 2020 at 12:58):
(not related to the main question, but the /_search segment should not be there - that's for POST searches, not GET ones with the parameters in the URL)
Muhammad Abubakar Ikram (Jun 04 2020 at 13:09):
@Michele Mottini yes you are right that's a typo. params could be in the body or in the URL. in POST or in GET request respectively.
@Amrit (In the context of SQL) The concept of GROUP BY is when you group the records you have to call a function on a field. That function will decide which record will be picked up in the result set. for example, we can SUM(amount) or COUNT(quantity) or MAX(date), MIN(date). So, what I want to achieve is GROUP BY Patient with MAX(issued).
Amrit (Jun 05 2020 at 01:01):
Ah.. Since there is apparently no grouping in FHIR, you would have to provide a search value for your variable and loop through all possible values. Assuming all relevant search parameters are supported by your server, for your specific use case, the following should work, so long you iterate over all possible logical ids of patients.
GET [base]/DiagnosticReport?_patient=[logical id]&_sort=-issued&_count=1
The above uses the issued date which is more akin to a business version date. You could also use the record version date. So if that's what you need:
GET [base]/DiagnosticReport?_patient=[logical id]&_sort=-_lastUpdated&_count=1
Muhammad Abubakar Ikram (Jun 05 2020 at 06:15):
@Amrit I need to get DiagnosticReport for all the Patients available, not for just one patient
Last updated: Apr 12 2022 at 19:14 UTC
