FHIR 查詢折疊模式

發行項
03/16/2024

本文說明允許 FHIR 中有效查詢折疊的 Power Query 模式。假設您已熟悉使用適用於 FHIR 的 Power Query 連接器，並瞭解 FHIR 中 Power Query 折疊的基本動機和原則。

如何使用本檔

本檔中的範例清單並不詳盡，且未涵蓋查詢折疊的所有搜尋參數。不過，我們提供您可能遇到的查詢和參數類型範例。當您建構篩選查詢表示式時，請考慮您要篩選的參數是否為：

基本類型（例如 Patient.birthDate）
複雜類型，這將是 Power Query 中的記錄（例如 Patient.meta）
基本類型的陣列，這會是Power Query中的清單（例如 Patient.meta.profile）
複雜類型的陣列，這將是Power Query 中的數據表（例如 Observation.code.coding，其具有數個資料行）

然後參閱下列範例清單。此外，還有一些範例會將這些類型的篩選分頁結合在多層級的巢狀篩選語句中。最後，本文提供更複雜的篩選表達式，可折疊至復合搜尋參數。

在每個範例中，您會在每個篩選語句上方找到篩選表達式（Table.SelectRows）和右上方的批注 // Fold: ... ，說明表示式折疊的搜尋參數和值。

篩選基本類型

根屬性位於資源的根目錄，而且通常是基本類型（字串、日期等），但它們也可以是編碼字段（例如 Encoding.class）。本節顯示搜尋不同類型的基本根層級屬性的範例。

依出生日期篩選患者：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "birthdate=lt1980-01-01"
    FilteredPatients = Table.SelectRows(Patients, each [birthDate] < #date(1980, 1, 1))
in
    FilteredPatients

使用 and篩選病患的出生日期範圍，只有 20 世紀 70 年代：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "birthdate=ge1970-01-01&birthdate=lt1980-01-01"
    FilteredPatients = Table.SelectRows(Patients, each [birthDate] < #date(1980, 1, 1) and [birthDate] >= #date(1970, 1, 1))
in
    FilteredPatients

使用來 or篩選患者，而不是 20 世紀 70 年代：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "birthdate=ge1980-01-01,lt1970-01-01"
    FilteredPatients = Table.SelectRows(Patients, each [birthDate] >= #date(1980, 1, 1) or [birthDate] < #date(1970, 1, 1))
in
    FilteredPatients

使用中患者的替代搜尋：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "active=true"
    FilteredPatients = Table.SelectRows(Patients, each [active])
in
    FilteredPatients

替代搜尋作用中不真實的患者（可能包括遺漏）：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "active:not=true"
    FilteredPatients = Table.SelectRows(Patients, each [active] <> true)
in
    FilteredPatients

篩選只保留男性患者：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "gender=male"
    FilteredPatients = Table.SelectRows(Patients, each [gender] = "male")
in
    FilteredPatients

篩選只保留非男性的患者（包括其他）：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "gender:not=male"
    FilteredPatients = Table.SelectRows(Patients, each [gender] <> "male")
in
    FilteredPatients

篩選狀態為 final 的觀察值（程式代碼）：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "status=final"
    FilteredObservations = Table.SelectRows(Observations, each [status] = "final")
in
    FilteredObservations

篩選複雜類型

篩選上次更新的時間：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "_lastUpdated=2010-12-31T11:56:02.000+00:00"
    FilteredPatients = Table.SelectRows(Patients, each [meta][lastUpdated] = #datetimezone(2010, 12, 31, 11, 56, 2, 0, 0))
in
    FilteredPatients

根據類別系統和程式代碼篩選遭遇（程序代碼）：

let
    Encounters = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Encounter" ]}[Data],

    // Fold: "class=s|c"
    FilteredEncounters = Table.SelectRows(Encounters, each [class][system] = "s" and [class][code] = "c")
in
    FilteredEncounters

根據程式代碼篩選遭遇（編碼）：

let
    Encounters = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Encounter" ]}[Data],

    // Fold: "class=c"
    FilteredEncounters = Table.SelectRows(Encounters, each [class][code] = "c")
in
    FilteredEncounters

僅根據類別系統篩選遭遇（編碼）：

let
    Encounters = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Encounter" ]}[Data],

    // Fold: "class=s|"
    FilteredEncounters = Table.SelectRows(Encounters, each [class][system] = "s")
in
    FilteredEncounters

根據 Observation.subject.reference [參考] 篩選觀察：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "subject=Patient/1234"
    FilteredObservations = Table.SelectRows(Observations, each [subject][reference] = "Patient/1234")
in
    FilteredObservations

根據中的 Observation.subject.reference 變化篩選觀察值（參考）：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "subject=1234,Patient/1234,https://myfhirservice/Patient/1234"
    FilteredObservations = Table.SelectRows(Observations, each [subject][reference] = "1234" or [subject][reference] = "Patient/1234" or [subject][reference] = "https://myfhirservice/Patient/1234")
in
    FilteredObservations

篩選數量相等值（quantity）：

let
    ChargeItems = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "ChargeItem" ]}[Data],

    // Fold: "quantity=1"
    FilteredChargeItems = Table.SelectRows(ChargeItems, each [quantity][value] = 1)
in
    FilteredChargeItems

篩選大於值的數量（quantity）：

let
    ChargeItems = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "ChargeItem" ]}[Data],

    // Fold: "quantity=gt1.001"
    FilteredChargeItems = Table.SelectRows(ChargeItems, each [quantity][value] > 1.001)
in
    FilteredChargeItems

篩選具有值系統和程式代碼的數量（quantity）：

let
    ChargeItems = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "ChargeItem" ]}[Data],

    // Fold: "quantity=lt1.001|s|c"
    FilteredChargeItems = Table.SelectRows(ChargeItems, each [quantity][value] < 1.001 and [quantity][system] = "s" and [quantity][code] = "c")
in
    FilteredChargeItems

篩選期間，從（period）開始：

let
    Consents = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Consent" ]}[Data],

    // Fold: "period=sa2010-01-01T00:00:00.000+00:00"
    FiltertedConsents = Table.SelectRows(Consents, each [provision][period][start] > #datetimezone(2010, 1, 1, 0, 0, 0, 0, 0))
in
    FiltertedConsents

篩選期間，結束於（period）：

let
    Consents = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Consent" ]}[Data],

    // Fold: "period=eb2010-01-01T00:00:00.000+00:00"
    FiltertedConsents = Table.SelectRows(Consents, each [provision][period][end] < #datetimezone(2010, 1, 1, 0, 0, 0, 0, 0))
in
    FiltertedConsents

篩選複雜類型的文字欄位：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "code:text=t"
    FilteredObservations = Table.SelectRows(Observations, each [code][text] = "t")
in
    FilteredObservations

篩選文字欄位（開頭為）：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "code:text=t"
    FilteredObservations = Table.SelectRows(Observations, each Text.StartsWith([code][text], "t"))
in
    FilteredObservations

篩選清單屬性

篩選設定檔上的病患：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "_profile=http://myprofile"
    FilteredPatients = Table.SelectRows(Patients, each List.MatchesAny([meta][profile], each _ = "http://myprofile"))
in
    FilteredPatients

篩選 Category 上的過敏Intolerance：

let
    AllergyIntolerances = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "AllergyIntolerance" ]}[Data],

    // Fold: "category=food"
    FilteredAllergyIntolerances = Table.SelectRows(AllergyIntolerances, each List.MatchesAny([category], each _ = "food"))
in
    FilteredAllergyIntolerances

篩選遺漏類別的過敏Intolerance：

let
    AllergyIntolerances = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "AllergyIntolerance" ]}[Data],

    // Fold: "category:missing=true"
    FilteredAllergyIntolerances = Table.SelectRows(AllergyIntolerances, each List.MatchesAll([category], each _ = null))
in
    FilteredAllergyIntolerances

在較簡單形式的遺漏類別上篩選過敏Intolerance：

let
    AllergyIntolerances = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "AllergyIntolerance" ]}[Data],

    // Fold: "category:missing=true"
    FilteredAllergyIntolerances = Table.SelectRows(AllergyIntolerances, each [category] = null)
in
    FilteredAllergyIntolerances

篩選數據表屬性

篩選確切家族名稱上的病患：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "family:exact=Johnson"
    FilteredPatients = Table.SelectRows(Patients, each Table.MatchesAnyRows([name], each [family] = "Johnson"))
in
    FilteredPatients

篩選家族名稱開頭為的病患：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "family=John"
    FilteredPatients = Table.SelectRows(Patients, each Table.MatchesAnyRows([name], each Text.StartsWith([family], "John")))
in
    FilteredPatients

篩選家族名稱上的病患開頭為 John 或 Paul：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "family=John,Paul"
    FilteredPatients = Table.SelectRows(Patients, each Table.MatchesAnyRows([name], each Text.StartsWith([family], "John") or Text.StartsWith([family], "Paul")))
in
    FilteredPatients

篩選家族名稱上的病患開頭為 John ，並以開頭 Paul為：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "family=John&given=Paul"
    FilteredPatients = Table.SelectRows(
        Patients,
        each
            Table.MatchesAnyRows([name], each Text.StartsWith([family], "John")) and
            Table.MatchesAnyRows([name], each List.MatchesAny([given], each Text.StartsWith(_, "Paul"))))
in
    FilteredPatients

篩選目標到期日：

let
    Goals = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Goal" ]}[Data],

    // Fold: "target-date=gt2020-03-01"
    FilteredGoals = Table.SelectRows(Goals, each Table.MatchesAnyRows([target], each [due][date] > #date(2020,3,1)))
in
    FilteredGoals

篩選識別碼上的病患：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "identifier=s|v"
    FilteredPatients = Table.SelectRows(Patients, each Table.MatchesAnyRows([identifier], each [system] = "s" and _[value] = "v"))
in
    FilteredPatients

篩選觀察程序代碼（CodeableConcept）：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "code=s|c"
    FilteredObservations = Table.SelectRows(Observations, each Table.MatchesAnyRows([code][coding], each [system] = "s" and [code] = "c"))
in
    FilteredObservations

篩選觀察程式代碼和文字（CodeableConcept）：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "code:text=t&code=s|c"
    FilteredObservations = Table.SelectRows(Observations, each Table.MatchesAnyRows([code][coding], each [system] = "s" and [code] = "c") and [code][text] = "t")
in
    FilteredObservations

篩選多層級巢狀屬性

篩選家族名稱上的病患開頭為 John ，並以開頭 Paul為：

let
    Patients = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Patient" ]}[Data],

    // Fold: "family=John&given=Paul"
    FilteredPatients =
        Table.SelectRows(
            Patients,
            each
                Table.MatchesAnyRows([name], each Text.StartsWith([family], "John")) and
                Table.MatchesAnyRows([name], each List.MatchesAny([given], each Text.StartsWith(_, "Paul"))))
in
    FilteredPatients

只篩選觀察中的重要跡象：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "category=vital-signs"
    FilteredObservations = Table.SelectRows(Observations, each Table.MatchesAnyRows([category], each Table.MatchesAnyRows([coding], each [code] = "vital-signs")))
in
    FilteredObservations

使用系統與程式代碼篩選類別編碼的觀察：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "category=s|c"
    FilteredObservations = Table.SelectRows(Observations, each Table.MatchesAnyRows([category], each Table.MatchesAnyRows([coding], each [system] = "s" and [code] = "c")))
in
    FilteredObservations

篩選多個類別的觀察（OR）：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "category=s1|c1,s2|c2"
    FilteredObservations =
        Table.SelectRows(
            Observations,
            each
                Table.MatchesAnyRows(
                    [category],
                    each
                        Table.MatchesAnyRows(
                            [coding],
                            each 
                                ([system] = "s1" and [code] = "c1") or
                                ([system] = "s2" and [code] = "c2"))))
in
    FilteredObservations

篩選資料表中的巢狀清單：

let
    AuditEvents = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "AuditEvent" ]}[Data],

    // Fold: "policy=http://mypolicy"
    FilteredAuditEvents = Table.SelectRows(AuditEvents, each Table.MatchesAnyRows([agent], each List.MatchesAny([policy], each _ = "http://mypolicy")))
in
    FilteredAuditEvents

使用複合搜尋參數進行篩選

FHIR 具有複合搜尋參數，可同時篩選資源內或資源根目錄內複雜類型的多個字段。例如，您可以搜尋具有特定程式代碼 的觀察值和 特定值（ code-value-quantity 搜尋參數）。適用於 FHIR 的 Power Query 連接器會嘗試辨識對應至這類複合搜尋參數的篩選表達式。本節列出這些模式的一些範例。在分析 FHIR 數據的內容中，它尤其是感興趣的資源上的 Observation 複合搜尋參數。

篩選程式代碼和值數量上的觀察，主體高度大於 150：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "code-value-quantity=http://loinc.org|8302-2$gt150"
    FilteredObservations = Table.SelectRows(Observations, each Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8302-2") and [value][Quantity][value] > 150)
in
    FilteredObservations

篩選觀察元件程式代碼和值數量，收縮血壓大於 140：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "component-code-value-quantity=http://loinc.org|8480-6$gt140"
    FilteredObservations = Table.SelectRows(Observations, each Table.MatchesAnyRows([component], each Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8480-6") and [value][Quantity][value] > 140))
in
    FilteredObservations

篩選多個元件代碼值數量（AND）、大於 90 的腹瀉血壓和 大於 140 的收縮血壓：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "component-code-value-quantity=http://loinc.org|8462-4$gt90&component-code-value-quantity=http://loinc.org|8480-6$gt140"
    FilteredObservations = 
        Table.SelectRows(
            Observations, 
            each 
                Table.MatchesAnyRows(
                    [component],
                    each 
                        Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8462-4") and [value][Quantity][value] > 90) and 
                        Table.MatchesAnyRows([component], each Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8480-6") and [value][Quantity][value] > 140))
in
    FilteredObservations

篩選多個元件代碼值數量（OR）、大於 90 的診斷血壓或 大於 140 的收縮血壓：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "component-code-value-quantity=http://loinc.org|8462-4$gt90,http://loinc.org|8480-6$gt140"
    FilteredObservations = 
        Table.SelectRows(
            Observations, 
            each 
                Table.MatchesAnyRows(
                    [component], 
                    each 
                        (Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8462-4") and [value][Quantity][value] > 90) or
                         Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8480-6") and [value][Quantity][value] > 140 ))
in
    FilteredObservations

篩選資源根目錄或元件數位上程式代碼值數量的觀察：

let
    Observations = Fhir.Contents("https://myfhirserver.azurehealthcareapis.com", null){[Name = "Observation" ]}[Data],

    // Fold: "combo-code-value-quantity=http://loinc.org|8302-2$gt150"
    FilteredObservations =
        Table.SelectRows(
            Observations,
            each 
                (Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8302-2") and [value][Quantity][value] > 150) or
                (Table.MatchesAnyRows([component], each Table.MatchesAnyRows([code][coding], each [system] = "http://loinc.org" and [code] = "8302-2") and [value][Quantity][value] > 150)))
in
    FilteredObservations

摘要

查詢折疊會將Power Query篩選表達式轉換成 FHIR 搜尋參數。適用於 FHIR 的 Power Query 連接器會辨識特定模式，並嘗試識別相符的搜尋參數。辨識這些模式可協助您撰寫更有效率的Power Query 運算式。

下一步

在本文中，我們已檢閱折疊至 FHIR 搜尋參數的一些篩選表達式類別。接下來，請閱讀建立 FHIR 資源之間的關聯性。

FHIR Power Query 關聯性

共用方式為