用于提取JSON数据的PostgreSQL查询

Question

我在codemap列中有json数据，需要从其中提取来自不同编码参数类别的代码(属于需要删除、修改或添加的代码)，例如cpt drg、修饰符、secondaryDiagnosis、principalDiagnosis、secondaryProcedure、观察值小时、dischargeStatus、主要过程、hcpcs

下面是我的输入和预期数据的一些图像：

下面是我的JSON数据的外观

{
  "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"
        }
      }
    ]
  }
}

下面是我的输入表和输出表的样子。

108565-input-data-1.jpg 108546-input-data-2.jpg 108547-output.jpg

输入数据：

​

​

预期输出：

​

​

我在代码上的尝试：

SELECT JSONB_ARRAY_ELEMENTS(codemap->'secondardiagnosis' ->'added' ->> 'code') 
    AS secondarydiagnosisaddedcodeadded, * 
  FROM qa_score_report

我没有得到任何输出。我可以尝试什么来解决这个问题？

Answer 1

您为数组函数提供了一个字符串(code)。首先解析数组added，然后使用结果集解析code

SELECT jsonb_array_elements(codemap->'secondaryDiagnosis'->'added')->'code' 
FROM qa_score_report;

将code聚合到一行中

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

演示：db<>fiddle