Postgres 13

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?.

WITH qa_score_report (codemap) AS (

VALUES ('{"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)

)

SELECT jsonb_array_elements(codemap->'secondaryDiagnosis'->'added')->'code'

FROM qa_score_report;

?column?
"E1165"
"Z136"
"Z1211"

SELECT 3

WITH qa_score_report (codemap) AS (

)

SELECT array_to_string(array_agg(trim('"' FROM j::text)),',') FROM

(SELECT jsonb_array_elements(codemap->'secondaryDiagnosis'->'added')->'code'

FROM qa_score_report) i (j);

array_to_string
E1165,Z136,Z1211

SELECT 1