Open kapsner opened 2 years ago
Would it be ok if we add a filter on date to systematically filter and search. Similar to the following where an effectivedate. However the Query need to be tested
SELECT
r1.fhir_id AS "Fall.Versorgungsstellenkontakt.Aufnahmenummer",
r2.cd_system ->> 'code' AS "Laborbefund.Laboruntersuchung.Code"
FROM ( SELECT * FROM (
SELECT
fhir_id,
to_timestamp(jsonb_path_query(DATA, '$.period') ->> 'start', 'YYYY-MM-DDTHH:MI:SS') AS fhir_start_date,
jsonb_array_elements_text(jsonb_path_query(DATA, '$.meta.profile')) AS fhir_profile
FROM resources
WHERE TYPE = 'Encounter') AS r_intermediate ) r1
LEFT JOIN (
SELECT
REPLACE(jsonbdata2 -> 'encounter' ->> 'reference', 'Encounter/', '') AS eid,
cd_system FROM (
SELECT
DATA AS jsonbdata2,
jsonb_array_elements(jsonb_path_query(DATA, '$.code.coding')) AS cd_system,
jsonb_array_elements(jsonb_path_query(DATA, '$.category.coding')) AS cd_category,
jsonb_array_elements(jsonb_path_query(DATA, '$.effectiveDatetime')) AS cd_effectivedate
FROM resources
WHERE TYPE = 'Observation') r3
WHERE r3.cd_system ->> 'system' = 'http://loinc.org/' AND
r3.cd_category ->> 'code' = '26436-6' AND
r3.cd_effectivedate ->> 'date' BETWEEN TO_DATE('2020-01-01T00:00:00','YYYY-MM-DD"T"HH24:MI:SS') AND TO_DATE('2020-01-02T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')
) r2 ON
r2.eid = r1.fhir_id
WHERE r1.fhir_profile = 'https://fhir.miracum.org/core/StructureDefinition/Versorgungsfall';
@rajesh-murali thanks for your suggestion. We do that already by replacing AS r_intermediate
with AS r_intermediate WHERE r_intermediate.fhir_start_date BETWEEN .... AND. ....
However, that doesn't help as well.
FYI @KoesterH
This SQL statement takes very very long to return the results:
It would be great, if the fhir-gateway could somehow be "enhanced" in a way, that this and similar SQL statements can be executed in a reasonable amount of time.
Some suggestions exist already, e.g.