用于总结错误数据的Postgres SQL查询

Question

我在列codemap中有json数据，需要从不同的编码参数类别(如cpt、drg、modifiers、secondaryDiagnosis、principalDiagnosis、secondaryProcedure、observationhours、dischargeStatus、principalprocedure、hcpcs)中提取代码(属于removed、revised或added)。

请在我的小提琴输入和输出片段下面找到：

输入

WITH qareport (ctextid,vbillid,codemap) AS (
VALUES(('20211','1345','{"cpt": {"added": [], "correct": [], "removed": [], "revised": []}, "drg": {"correct": [], "revised": []}, "poa": {"correct": [], "revised": []}, "hcpcs": {"added": [], "correct": [], "removed": [], "revised": []}, "modifiers": {"added": [], "correct": [], "removed": [], "revised": []}, "dischargeStatus": {"correct": [], "revised": []}, "observationHours": {"correct": [], "revised": []}, "injectionInfusion": {"added": [], "correct": [], "removed": [], "revised": []}, "principalDiagnosis": {"correct": [{"code": "E559", "description": "VITAMIN D DEFICIENCY, UNSPECIFIED (ICD-10) "}], "revised": []}, "principalProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "secondaryDiagnosis": {"added": [], "correct": [{"code": "E538", "description": "DEFICIENCY OF OTHER SPECIFIED B GROUP VITAMINS (ICD-10) "}, {"code": "E119", "description": "TYPE 2 DIABETES MELLITUS WITHOUT COMPLICATIONS (ICD-10) "}, {"code": "E782", "description": "MIXED HYPERLIPIDEMIA (ICD-10) "}, {"code": "M7910", "description": "MYALGIA, UNSPECIFIED SITE (ICD-10) "}, {"code": "Z7984", "description": "LONG TERM (CURRENT) USE OF ORAL HYPOGLYCEMIC DRUGS (ICD-10) "}, {"code": "Z79899", "description": "OTHER LONG TERM (CURRENT) DRUG THERAPY (ICD-10) "}, {"code": "Z9884", "description": "BARIATRIC SURGERY STATUS (ICD-10) "}], "removed": [], "revised": []}, "secondaryProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "facilityEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": []}, "professionalEvaluationManagement": {"added": [], "correct": [{"code": "99204", "description": "OFFICE OR OTHER OUTPATIENT VISIT FOR THE EVALUATION AND MANAGEMENT OF A NEW PATIENT, WHICH REQUIRES THESE THREE KEY COMPONENTS: A COMPREHENSIVE HISTORY; A COMPREHENSIVE EXAMINATION; AND MEDICAL DECISI"}], "removed": [], "revised": []}}'::jsonb),
('20211','1346','{"cpt": {"added": [], "correct": [{"code": "99497", "description": "ADVANCE CARE PLANNING FIRST 30 MIN"}, {"code": "96160", "description": "ADMINISTRATION OF PATIENT-FOCUSED HEALTH RISK ASSESSMENT INSTRUMENT (EG, HEALTH HAZARD APPRAISAL) WITH SCORING AND DOCUMENTATION, PER STANDARDIZED INSTRUMENT"}], "removed": [], "revised": []}, "drg": {"correct": [], "revised": []}, "poa": {"correct": [], "revised": []}, "hcpcs": {"added": [{"code": "G0402", "description": "INITIAL PREVENTIVE EXAM"}], "correct": [], "removed": [], "revised": []}, "modifiers": {"added": [{"code": "25", "description": "Significant Separately Identifiable E&M Service by the Same Physician on the Same Day"}], "correct": [], "removed": [], "revised": []}, "dischargeStatus": {"correct": [], "revised": []}, "observationHours": {"correct": [], "revised": []}, "injectionInfusion": {"added": [], "correct": [], "removed": [], "revised": []}, "principalDiagnosis": {"correct": [{"code": "Z0000", "description": "ENCNTR FOR GENERAL ADULT MEDICAL EXAM W/O ABNORMAL FINDINGS (ICD-10) "}], "revised": []}, "principalProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "secondaryDiagnosis": {"added": [{"code": "E1165", "description": "TYPE 2 DIABETES MELLITUS WITH HYPERGLYCEMIA (ICD-10) "}, {"code": "Z136", "description": "ENCOUNTER FOR SCREENING FOR CARDIOVASCULAR DISORDERS (ICD-10) "}, {"code": "Z1211", "description": "ENCOUNTER FOR SCREENING FOR MALIGNANT NEOPLASM OF COLON (ICD-10) "}], "correct": [{"code": "M25512", "description": "PAIN IN LEFT SHOULDER (ICD-10) "}, {"code": "Z7984", "description": "LONG TERM (CURRENT) USE OF ORAL HYPOGLYCEMIC DRUGS (ICD-10) "}, {"code": "Z791", "description": "LONG TERM (CURRENT) USE OF NON-STEROIDAL NON-INFLAM (NSAID) (ICD-10) "}, {"code": "Z0000", "description": "ENCNTR FOR GENERAL ADULT MEDICAL EXAM W/O ABNORMAL FINDINGS (ICD-10) "}, {"code": "M25512", "description": "PAIN IN LEFT SHOULDER (ICD-10) "}, {"code": "Z7984", "description": "LONG TERM (CURRENT) USE OF ORAL HYPOGLYCEMIC DRUGS (ICD-10) "}, {"code": "Z791", "description": "LONG TERM (CURRENT) USE OF NON-STEROIDAL NON-INFLAM (NSAID) (ICD-10) "}, {"code": "Z1339", "description": "ENCNTR SCREEN EXAM FOR OTHER MENTAL HLTH AND BEHAVRL DISORD (ICD-10) "}], "removed": [], "revised": [{"to": {"code": "M25512", "description": "PAIN IN LEFT SHOULDER (ICD-10) "}, "from": {"code": "Z79899", "description": "OTHER LONG TERM (CURRENT) DRUG THERAPY (ICD-10) "}}]}, "secondaryProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "facilityEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": []}, "professionalEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": [{"to": {"code": "99213", "description": "OFF/OP VIS, EST PT, 2KEY COMP: EXPAND PROB HX; EXPAN"}, "from": {"code": "99396", "description": "PERI COMP PREV MED E&M W/HX/EXAM, EST PT;40-64YR"}}]}}'::jsonb),
('20211','1347','{"cpt": {"added": [], "correct": [{"code": "11055", "description": "PARING OR CUTING OF BENIGN HYPERKERATOTIC LESION, SINGLE"}, {"code": "11720", "description": "DEBRIDEMENT OF NAILS, ONE TO FIVE"}, {"code": "11719", "description": "TRIMMING OF NONDYSTROPHIC NAILS"}], "removed": [], "revised": []}, "drg": {"correct": [], "revised": []}, "poa": {"correct": [], "revised": []}, "hcpcs": {"added": [], "correct": [], "removed": [], "revised": []}, "modifiers": {"added": [], "correct": [{"code": "Q8", "description": "2 CLASS B FINDINGS"}, {"code": "LT", "description": "LEFT SIDE"}, {"code": "Q8", "description": "2 CLASS B FINDINGS"}, {"code": "59", "description": "DISTINCT PROCEDURAL SERVICE"}, {"code": "T1", "description": "LEFT FOOT, SECOND DIGIT"}, {"code": "99", "description": "MULTIPLE MODIFIERS"}, {"code": "Q8", "description": "2 CLASS B FINDINGS"}, {"code": "T2", "description": "LEFT FOOT, THIRD DIGIT"}, {"code": "T7", "description": "RIGHT FOOT, THIRD DIGIT"}, {"code": "99", "description": "MULTIPLE MODIFIERS"}], "removed": [], "revised": []}, "dischargeStatus": {"correct": [], "revised": []}, "observationHours": {"correct": [], "revised": []}, "injectionInfusion": {"added": [], "correct": [], "removed": [], "revised": []}, "principalDiagnosis": {"correct": [{"code": "I872", "description": "VENOUS INSUFFICIENCY (CHRONIC) (PERIPHERAL) (ICD-10) "}], "revised": []}, "principalProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "secondaryDiagnosis": {"added": [], "correct": [{"code": "L84", "description": "CORNS AND CALLOSITIES (ICD-10) "}, {"code": "I872", "description": "VENOUS INSUFFICIENCY (CHRONIC) (PERIPHERAL) (ICD-10) "}, {"code": "B351", "description": "TINEA UNGUIUM (ICD-10) "}, {"code": "M79672", "description": "PAIN IN LEFT FOOT (ICD-10) "}, {"code": "I872", "description": "VENOUS INSUFFICIENCY (CHRONIC) (PERIPHERAL) (ICD-10) "}, {"code": "L603", "description": "NAIL DYSTROPHY (ICD-10) "}, {"code": "I83899", "description": "VARICOS VN UNSP LOWER EXTREMITY WITH OTHER COMPLICATIONS (ICD-10) "}, {"code": "E663", "description": "OVERWEIGHT (ICD-10) "}, {"code": "Z79899", "description": "OTHER LONG TERM (CURRENT) DRUG THERAPY (ICD-10) "}, {"code": "Z7982", "description": "LONG TERM (CURRENT) USE OF ASPIRIN (ICD-10) "}, {"code": "Z6841", "description": "BODY MASS INDEX [BMI]40.0-44.9, ADULT (ICD-10) "}], "removed": [], "revised": [{"to": {"code": "I10", "description": "ESSENTIAL (PRIMARY) HYPERTENSION (ICD-10) "}, "from": {"code": "I129", "description": "HYPERTENSIVE CHRONIC KIDNEY DISEASE W STG 1-4/UNSP CHR KDNY (ICD-10) "}}, {"to": {"code": "Z8673", "description": "PRSNL HX OF TIA (TIA), AND CEREB INFRC W/O RESID DEFICITS (ICD-10) "}, "from": {"code": "N189", "description": "CHRONIC KIDNEY DISEASE, UNSPECIFIED (ICD-10) "}}]}, "secondaryProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "facilityEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": []}, "professionalEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": []}}'::jsonb),
('20214','4116','{"cpt": {"added": [], "correct": [{"code": "3008F", "description": "BODY MASS INDEX DOCUMENTED"}], "removed": [], "revised": []}, "drg": {"correct": [], "revised": []}, "poa": {"correct": [], "revised": []}, "hcpcs": {"added": [], "correct": [], "removed": [], "revised": []}, "modifiers": {"added": [], "correct": [{"code": "95", "description": "TELEHEALTH"}], "removed": [], "revised": []}, "dischargeStatus": {"correct": [], "revised": []}, "observationHours": {"correct": [], "revised": []}, "injectionInfusion": {"added": [], "correct": [], "removed": [], "revised": []}, "principalDiagnosis": {"correct": [{"code": "J029", "description": "ACUTE PHARYNGITIS, UNSPECIFIED (ICD-10) "}], "revised": []}, "principalProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "secondaryDiagnosis": {"added": [], "correct": [{"code": "J302", "description": "OTHER SEASONAL ALLERGIC RHINITIS (ICD-10) "}, {"code": "E669", "description": "OBESITY, UNSPECIFIED (ICD-10) "}, {"code": "Z6833", "description": "BODY MASS INDEX [BMI] 33.0-33.9, ADULT (ICD-10) "}, {"code": "Z87891", "description": "PERSONAL HISTORY OF NICOTINE DEPENDENCE (ICD-10) "}, {"code": "Z713", "description": "DIETARY COUNSELING AND SURVEILLANCE (ICD-10) "}, {"code": "Z79899", "description": "OTHER LONG TERM (CURRENT) DRUG THERAPY (ICD-10) "}], "removed": [{"code": "H9201", "description": "OTALGIA, RIGHT EAR (ICD-10) "}], "revised": []}, "secondaryProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "facilityEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": []}, "professionalEvaluationManagement": {"added": [], "correct": [{"code": "99214", "description": "OFFICE/OUTPT VISIT, EST, DETAILED"}], "removed": [], "revised": []}}'::jsonb)
)

预期产出

WITH output (ctextid,vbillid,typeofcorrection,errorpara,oldvalue,newvalue) AS
(VALUES ('20211','1346','Added','HCPCS','','G0402'),
('20211','1346','Added','Modifiers','','25'),
('20211','1346','Added','Secondary Diagnosis','','E1165,Z136,Z1211'),
('20211','1346','Revised','Secondary Diagnosis','Z79899','M25512'),
('20211','1346','Revised','professionalEvaluationManagement','99396','99213'),
('20211','1347','Revised','Secondary Diagnosis','I129','I10'),
('20211','1347','Revised','Secondary Diagnosis','N189','Z8673'),
('20214','4116','Removed','Secondary Diagnosis','','H9201')
)

我尝试了以下查询：

SELECT  ctextid,vbillid,jsonb_array_elements(qaauditcodemap->'cpt'->'revised')->'from'->>'code' AS cptrevised_from,
       jsonb_array_elements(qaauditcodemap->'cpt'->'revised')->'to'->>'code' AS cptrevised_to,
       jsonb_array_elements(qaauditcodemap->'cpt'->'added')->>'code' AS cptadded,
       jsonb_array_elements(qaauditcodemap->'cpt'->'removed')->>'code' AS cptremoved,
       jsonb_array_elements(qaauditcodemap->'hcpcs'->'revised')->'from'->>'code' AS hcpcsrevised_from,
       jsonb_array_elements(qaauditcodemap->'hcpcs'->'revised')->'to'->>'code' AS hcpcsrevised_to,
       jsonb_array_elements(qaauditcodemap->'hcpcs'->'added')->>'code' AS hcpcsadded,
         jsonb_array_elements(qaauditcodemap->'hcpcs'->'removed')->>'code' AS hcpcsremoved,          jsonb_array_elements(qaauditcodemap->'modifiers'->'revised')->'from'->>'code' AS modifiersrevised_from,
       jsonb_array_elements(qaauditcodemap->'modifiers'->'revised')->'to'->>'code' AS modifiersrevised_to,
       jsonb_array_elements(qaauditcodemap->'modifiers'->'added')->>'code' AS modifiersadded,
         jsonb_array_elements(qaauditcodemap->'modifiers'->'removed')->>'code' AS modifiersremoved,          jsonb_array_elements(qaauditcodemap->'observationHours'->'revised')->'from'->>'code' AS obervationHoursrevised_from,
       jsonb_array_elements(qaauditcodemap->'observationHours'->'revised')->'to'->>'code' AS observationHoursrevised_to,
       jsonb_array_elements(qaauditcodemap->'observationHours'->'added')->>'code' AS observationHoursadded,
         jsonb_array_elements(qaauditcodemap->'observationHours'->'removed')->>'code' AS obervationHoursremoved,         jsonb_array_elements(qaauditcodemap->'injectionInfusion'->'revised')->'from'->>'code' AS injectionInfusionrevised_from,
       jsonb_array_elements(qaauditcodemap->'injectionInfusion'->'revised')->'to'->>'code' AS injectionInfusionrevised_to,
       jsonb_array_elements(qaauditcodemap->'injectionInfusion'->'added')->>'code' AS injectionInfusionadded,
         jsonb_array_elements(qaauditcodemap->'injectionInfusion'->'removed')->>'code' AS injectionInfusionremoved,          jsonb_array_elements(qaauditcodemap->'principalDiagnosis'->'revised')->'from'->>'code' AS principalDiagnosisrevised_from,
       jsonb_array_elements(qaauditcodemap->'principalDiagnosis'->'revised')->'to'->>'code' AS principalDiagnosisrevised_to,
       jsonb_array_elements(qaauditcodemap->'principalDiagnosis'->'added')->>'code' AS principalDiagnosisadded,
         jsonb_array_elements(qaauditcodemap->'principalDiagnosis'->'removed')->>'code' AS principalDiagnosisremoved,        jsonb_array_elements(qaauditcodemap->'principalProcedure'->'revised')->'from'->>'code' AS principalProcedurerevised_from,
       jsonb_array_elements(qaauditcodemap->'principalProcedure'->'revised')->'to'->>'code' AS principalProcedurerevised_to,
       jsonb_array_elements(qaauditcodemap->'principalProcedure'->'added')->>'code' AS principalProcedureadded,
         jsonb_array_elements(qaauditcodemap->'principalProcedure'->'removed')->>'code' AS principalProcedureremoved
  
        FROM qareport

你能帮我达到预期的效果吗？

Answer 1

我希望了解您的结构查询和表和下面的查询帮助您。

您可以看到我的结构表和查询结果：[医]小提琴

没有聚合的：

with allow_category as (select unnest(array [
    'cpt',
    'drg',
    'modifiers',
    'secondaryDiagnosis',
    'principalDiagnosis',
    'secondaryProcedure',
    'observationhours',
    'dischargeStatus',
    'principalprocedure',
    'hcpcs',
    'professionalEvaluationManagement'
    ]::text[]) category)
select distinct ctextid,
                vbillid,
                initcap(kcy.key)                                                   as typeofcorrection,
                initcap(regexp_replace(kc.key, '([a-z])([A-Z])', '\1 \2', 'g'))    as errorpara,
                case when f ? 'from' then f -> 'from' ->> 'code' else '' end       as oldvalue,
                case when f ? 'to' then f -> 'to' ->> 'code' else f ->> 'code' end as newvalue
from qareport q
         cross join jsonb_each(q.codemap) as kc
         cross join jsonb_each(kc.value) as kcy
         join jsonb_array_elements(kcy.value) f on true
where kc.key in (select * from allow_category)
  and kcy.key in ('added', 'revised', 'removed')
  and (
        jsonb_array_length(kc.value -> 'added') > 0
        or jsonb_array_length(kc.value -> 'revised') > 0
        or jsonb_array_length(kc.value -> 'removed') > 0
    );

具有聚合的：

with allow_category as (select unnest(array [
    'cpt',
    'drg',
    'modifiers',
    'secondaryDiagnosis',
    'principalDiagnosis',
    'secondaryProcedure',
    'observationhours',
    'dischargeStatus',
    'principalprocedure',
    'hcpcs',
    'professionalEvaluationManagement'
    ]::text[]) category)
select distinct ctextid,
                vbillid,
                initcap(kcy.key)                                                   as typeofcorrection,
                initcap(regexp_replace(kc.key, '([a-z])([A-Z])', '\1 \2', 'g'))    as errorpara,
                case when f ? 'from' then f -> 'from' ->> 'code' else '' end       as oldvalue,
                case
                    when f ? 'to' then f -> 'to' ->> 'code'
                    else string_agg(f ->> 'code', ',')
                         over (partition by ctextid, vbillid, kcy.key, kc.key) end as newvalue
from qareport q
         cross join jsonb_each(q.codemap) as kc
         cross join jsonb_each(kc.value) as kcy
         join jsonb_array_elements(kcy.value) f on true
where kc.key in (select * from allow_category)
  and kcy.key in ('added', 'revised', 'removed')
  and (
        jsonb_array_length(kc.value -> 'added') > 0
        or jsonb_array_length(kc.value -> 'revised') > 0
        or jsonb_array_length(kc.value -> 'removed') > 0
    );