Postgres SQL Query to Summarize Errors Data

Question

I have the json data in the column codemap from which the codes (belonging to removed, revised or added) need to be extracted, from the different coding parameter categories such as cpt, drg, modifiers, secondaryDiagnosis, principalDiagnosis, secondaryProcedure, observationhours , dischargeStatus ,principalprocedure,hcpcs) need to be extracted

Please find below my fiddle input and output snippets:

Input

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

Expected output

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

I tried the following Query:

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

Can you please help me to achieve the desired result?

Hi everyone...Please let me know if someone can help me here... — user3369545
– user3369545, Commented Oct 18, 2021 at 12:39

Pooya · Accepted Answer · 2021-10-19 07:48:12Z

1

+50

I hope understand your structure query and table and below query help you.

You can see my structure table and query result: dbfiddle

Without aggregate:

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 aggregate:

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

edited Oct 19, 2021 at 7:48

answered Oct 19, 2021 at 7:17

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3369545 Over a year ago

Hi @Pooya: Really appreciate your response!...In case I want the added secondary diagnosis codes to be added one in each line and not as an aggregation (by commas); can I take out the statement else string_agg(f ->> 'code', ',')

Pooya Over a year ago

Yes, You can remove aggregate and replace with f ->> 'code'

Pooya Over a year ago

I add aggregate because your expected output had 'E1165,Z136,Z1211'

Collectives™ on Stack Overflow

Postgres SQL Query to Summarize Errors Data

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related