-
Notifications
You must be signed in to change notification settings - Fork 13
/
fhir_observation_micro_test.sql
153 lines (143 loc) · 6.34 KB
/
fhir_observation_micro_test.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
-- Purpose: Generate a FHIR Observation resource for each unique specimen and test
-- found in microbiologyevents
-- Methods: uuid_generate_v5 --> requires uuid or text input, some inputs cast to text to fit
SELECT fhir_etl.fn_create_table_patient_dependent('observation_micro_test');
-- Group to avoid duplicate organisms showing up for a given specimen's test
WITH distinct_org AS (
SELECT DISTINCT
mi.micro_specimen_id AS mi_MICRO_SPECIMEN_ID
, CAST(mi.test_itemid AS TEXT) AS mi_TEST_ITEMID
, MAX(mi.test_name) AS mi_TEST_NAME
, MAX(mi.subject_id) AS mi_SUBJECT_ID
, MAX(mi.hadm_id) AS mi_HADM_ID
-- some charttimes are null, so take chartdate when not present
, MAX(CAST(COALESCE(mi.charttime, mi.chartdate) AS TIMESTAMPTZ)) AS mi_CHARTTIME
, CASE WHEN MIN(mi.org_itemid) IS NULL THEN NULL
ELSE
mi.test_itemid || '-' || mi.micro_specimen_id || '-' || mi.org_itemid
END as mi_ORGANISM
-- flag for whether a specimen has at least one organism growth
, CASE
WHEN MAX(mi.org_itemid) IS NULL AND MAX(mi.comments) = '___'
THEN NULL -- deid VALUES ARE captured IN valueCodeableConcept
WHEN MAX(mi.org_itemid) IS NULL AND MAX(mi.COMMENTS) IS NOT NULL
THEN MAX(mi.COMMENTS)
ELSE NULL
END AS valueString
-- if no test results and no comments(or deid) then add NullFlavor
, CASE
WHEN MAX(mi.org_itemid) IS NULL AND MAX(mi.comments) = '___'
THEN 'MSK' -- Masked
WHEN MAX(mi.org_itemid) IS NULL AND MAX(mi.COMMENTS) IS NULL
THEN 'NI' -- NO Information
ELSE NULL
END AS valueCodeableConcept
FROM
mimiciv_hosp.microbiologyevents mi
GROUP BY
test_itemid
, micro_specimen_id
, org_itemid
), grouped_org AS (
SELECT
mi_MICRO_SPECIMEN_ID
, mi_TEST_ITEMID
, MAX(mi_TEST_NAME) AS mi_TEST_NAME
, MAX(mi_SUBJECT_ID) AS mi_SUBJECT_ID
, MAX(mi_HADM_ID) AS mi_HADM_ID
, MAX(mi_CHARTTIME) AS mi_CHARTTIME
, MAX(valueString) AS valueString
, MAX(valueCodeableConcept) AS valueCodeableConcept
-- only include organism list if specimen had at least one organism growth
-- and there is at least one non empty mi_ORGANISM value
, CASE WHEN MAX(valueString) IS NULL AND MAX(mi_ORGANISM) IS NOT NULL THEN
json_agg(
jsonb_build_object('reference',
'Observation/' || uuid_generate_v5(ns_observation_micro_org.uuid, mi_ORGANISM)
)
)
ELSE NULL END AS fhir_ORGANISM
FROM
distinct_org
LEFT JOIN fhir_etl.uuid_namespace ns_observation_micro_org
ON ns_observation_micro_org.name = 'ObservationMicroOrg'
GROUP BY
mi_MICRO_SPECIMEN_ID
, mi_TEST_ITEMID
, ns_observation_micro_org.uuid
), fhir_observation_micro_test AS (
SELECT
mi_MICRO_SPECIMEN_ID
, mi_TEST_ITEMID
, mi_TEST_NAME
, mi_SUBJECT_ID
, mi_HADM_ID
, mi_CHARTTIME
, valueString
, valueCodeableConcept
, fhir_ORGANISM
-- UUID references
, uuid_generate_v5(ns_observation_micro_test.uuid, mi_MICRO_SPECIMEN_ID|| '-' || mi_TEST_ITEMID) AS uuid_MICRO_TEST
, uuid_generate_v5(ns_patient.uuid, CAST(mi_SUBJECT_ID AS TEXT)) AS uuid_SUBJECT_ID
, uuid_generate_v5(ns_encounter.uuid, CAST(mi_HADM_ID AS TEXT)) AS uuid_HADM_ID
, uuid_generate_v5(ns_specimen.uuid, CAST(mi_MICRO_SPECIMEN_ID AS TEXT)) AS uuid_SPECIMEN
FROM
grouped_org
LEFT JOIN fhir_etl.uuid_namespace ns_patient
ON ns_patient.name = 'Patient'
LEFT JOIN fhir_etl.uuid_namespace ns_encounter
ON ns_encounter.name = 'Encounter'
LEFT JOIN fhir_etl.uuid_namespace ns_observation_micro_test
ON ns_observation_micro_test.name = 'ObservationMicroTest'
LEFT JOIN fhir_etl.uuid_namespace ns_specimen
ON ns_specimen.name = 'SpecimenMicro'
)
INSERT INTO mimic_fhir.observation_micro_test
SELECT
uuid_MICRO_TEST AS id
, uuid_SUBJECT_ID AS patient_id
, jsonb_strip_nulls(jsonb_build_object(
'resourceType', 'Observation'
, 'id', uuid_MICRO_TEST
, 'meta', jsonb_build_object(
'profile', jsonb_build_array(
'http://mimic.mit.edu/fhir/mimic/StructureDefinition/mimic-observation-micro-test'
)
)
, 'status', 'final'
, 'category', jsonb_build_array(jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'system', 'http://terminology.hl7.org/CodeSystem/observation-category'
, 'code', 'laboratory'
, 'display', 'Laboratory'
))
))
, 'code', jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'system', 'http://mimic.mit.edu/fhir/mimic/CodeSystem/mimic-microbiology-test'
, 'code', mi_TEST_ITEMID
, 'display', mi_TEST_NAME
))
)
, 'subject', jsonb_build_object('reference', 'Patient/' || uuid_SUBJECT_ID)
, 'specimen', jsonb_build_object('reference', 'Specimen/' || uuid_SPECIMEN)
, 'encounter',
CASE WHEN uuid_HADM_ID IS NOT NULL THEN
jsonb_build_object('reference', 'Encounter/' || uuid_HADM_ID)
ELSE NULL END
, 'effectiveDateTime', mi_CHARTTIME
, 'hasMember', fhir_ORGANISM -- reference one to many organisms
, 'valueString', valueString -- result notes for tests with no organisms associated
, 'valueCodeableConcept', CASE WHEN valueCodeableConcept IS NOT NULL THEN
jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'code', valueCodeableConcept
, 'display', CASE WHEN valueCodeableConcept = 'MSK'
THEN 'masked' ELSE 'NoInformation' END
, 'system', 'http://terminology.hl7.org/CodeSystem/v3-NullFlavor'
))
)
ELSE NULL END
)) AS fhir
FROM
fhir_observation_micro_test;