Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table qareport
(
    ctextid text,
    vbillid text,
    codemap jsonb
);

insert
into qareport
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);

4 rows affected

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);

ctextid	vbillid	typeofcorrection	errorpara	oldvalue	newvalue
20211	1346	Added	Hcpcs		G0402
20211	1346	Added	Modifiers		25
20211	1346	Added	Secondary Diagnosis		E1165,Z136,Z1211
20211	1346	Revised	Professional Evaluation Management	99396	99213
20211	1346	Revised	Secondary Diagnosis	Z79899	M25512
20211	1347	Revised	Secondary Diagnosis	I129	I10
20211	1347	Revised	Secondary Diagnosis	N189	Z8673
20214	4116	Removed	Secondary Diagnosis		H9201