雪花SQL查询从JSON数据中提取代码

Question

希望你做得好！.我正在尝试提取CPT和从编码的encounters..An遭遇中提取的ICD-10代码，通过ctextid和v球..I的组合来表示复杂度值，在代码下提取名称和类型，并用逗号组合特定类型的代码(在列数据中都有)，...Please为输入和输出tables...Can查找下面的DDL，请在这里帮助我。

**Input**

Create table ##input
(ctextid int,
vbillid int,
data variant)

insert into ##input values
('7812','1012','"{\"complexity\": \"low\", \"Codes\": [{\"conf\": 0.9940267346365206, \"name\": \"87635\", \"type\": \"CPT\", \"xpath\": \"/div[2]/div/div[1]/div[3]/li/dd\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"nasal passage)   RAPID SARS COV 2 AG, QL IA, RESPIRATORY SPECIMEN  Result:   -  Rapid Covid: positive Discussion Notes Discussed lab results.  Discussed need for isolation or quarantine.  Discussed need to supplement with:  Vitamin\"}}, {\"conf\": 0.9991469971682254, \"name\": \"Z20828\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[3]/ul/li\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"\"}}, {\"conf\": 0.9969459941440534, \"name\": \"U071\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[1]/div[1]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"Assessment / Plan 1. COVID-19 U07.1: COVID-19  CORONAVIRUS (COVID-19): CARE INSTRUCTIONS\"}}], \"clinicalEncounterId\": 1535}"'),
('899','189','"{\"complexity\": \"low\", \"Codes\": [{\"conf\": 0.9944347091789092, \"name\": \"81002\", \"type\": \"CPT\", \"xpath\": \"/div[2]/div/div[1]/div[2]/ul/li\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"irregular menstruation  US, TRANSVAGINAL 2. Vaginitis and vulvovaginitis N76.0: Acute vaginitis  URINALYSIS, DIPSTICK  VAGINITIS: CARE INSTRUCTIONS  URINALYSIS, DIPSTICK  Results:   -  Leukocytes: Negative  -  Nitrite: negative  -  Glucose:\"}}, {\"conf\": 0.9999579891538954, \"name\": \"N760\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[1]/div[2]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"Assessment / Plan 1. Irregular periods N92.5: Other specified irregular menstruation  US, TRANSVAGINAL 2. Vaginitis and vulvovaginitis N76.0: Acute vaginitis  URINALYSIS, DIPSTICK  VAGINITIS: CARE INSTRUCTIONS  URINALYSIS\"}}], \"clinicalEncounterId\": 142}"'),
('615','488','"{\"complexity\": \"low\", \"Codes\": [{\"conf\": 0.9984004807623814, \"name\": \"87880\", \"type\": \"CPT\", \"xpath\": \"/div[2]/div/div[1]/div[1]/ul/li\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"/ Plan 1. Pain in throat R07.0: Pain in throat  RAPID STREP GROUP A, THROAT  Mucinex DM 30 mg-600 mg tablet,extended release 12 hr -  Take 1 tablet(s)\"}}, {\"conf\": 0.9996164227094804, \"name\": \"R070\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[1]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"oral route.     Qty: 1 blist pack(s) of 20     Refills: 0     Pharmacy: CALEDONIA PHARMACY   RAPID STREP GROUP A, THROAT  Result:   -  Strep: negative Return to Office Patient will return to the office\"}}], \"clinicalEncounterId\": 59}"'),
('899','1102','"{\"complexity\": \"low\", \"Codes\": [{\"conf\": 0.9968579484449694, \"name\": \"81002\", \"type\": \"CPT\", \"xpath\": \"/div[2]/div/div[1]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"Procedure code: 58120  VAGINAL BLEEDING AFTER MENOPAUSE: CARE INSTRUCTIONS  SURGICAL PATHOLOGY STUDY  URINALYSIS, DIPSTICK  URINALYSIS, DIPSTICK  Results:   -  Blood: Negative  -  Ketone: Negative  -  Protein: Negative  -  Leukocytes: Negative  -  Nitrite\"}}], \"clinicalEncounterId\": 146}"'),
('90123','8921','"{\"complexity\": \"medium\", \"Codes\": [{\"conf\": 0.9990972753776355, \"name\": \"J1100\", \"type\": \"CPT\", \"xpath\": \"/div[2]/div/dl/dt/dd/p\", \"section\": \"FROZENSECTIONHTML_ProcedureDocumentation\", \"extra_info\": {\"summary_ctxt_1\": \"the epidural space and ensure there is no vascular uptake. A 3cc mixture of 20 mg of Dexamethasone (2 cc) and 0.5 % lidocaine (1.5 cc) was injected into the epidural space\"}}, {\"conf\": 0.9988667373822541, \"name\": \"20610\", \"type\": \"CPT\", \"xpath\": \"/div[2]/div/dl/dt/dd/p\", \"section\": \"FROZENSECTIONHTML_ProcedureDocumentation\", \"extra_info\": {\"summary_ctxt_1\": \"0.5 % lidocaine (1.5 cc) was injected into the epidural space.  The needle was removed and the puncture site covered with a Band-Aid. The patient was given post-procedural\"}}, {\"conf\": 0.9996237289435855, \"name\": \"M5416\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[1]/div[3]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"2. Degeneration of cervical intervertebral disc M50.30: Other cervical disc degeneration, unspecified cervical region  3. Lumbar radiculopathy M54.16: Radiculopathy, lumbar region  4. Degeneration of lumbar intervertebral disc M51.\"}}, {\"conf\": 0.9997789864183196, \"name\": \"M5136\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[1]/div[4]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"Other cervical disc degeneration, unspecified cervical region  3. Lumbar radiculopathy M54.16: Radiculopathy, lumbar region  4. Degeneration of lumbar intervertebral disc M51.36: Other intervertebral disc degeneration, lumbar region  Discussion Notes Cervical\"}}, {\"conf\": 0.9999342438930537, \"name\": \"M5412\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[1]/div[1]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"Assessment / Plan 1. Cervical radiculopathy M54.12: Radiculopathy, cervical region  hydrocodone 5 mg-acetaminophen 325 mg tablet -\"}}, {\"conf\": 0.9999311996288434, \"name\": \"M5030\", \"type\": \"ICD-10-CM\", \"xpath\": \"/div[2]/div/div[1]/div[2]\", \"section\": \"FROZENSECTIONHTML_AssessmentPlan\", \"extra_info\": {\"summary_ctxt_1\": \"Authorization: Medicare-VA (Medicare) | NOTREQUIRED | Not Required for 62320, 62321  2. Degeneration of cervical intervertebral disc M50.30: Other cervical disc degeneration, unspecified cervical region  3\"}}], \"clinicalEncounterId\": 52}"')

**Output table**
create table ##output
(ctextid int,
vbillid int,
complexity varchar(40),
CPTcode varchar(100000),
diagnosiscode varchar(100000))

insert into ##output values
('7812','1012','low','87635','Z20828,U071'),
('899','189','low','81002','N760'),
('615','488','low','87880','R070'),
('899','1102','low','81002',''),
('90123','8921','medium','J1100,20610','M5416,M5136,M5412,M5030')

谢谢，阿伦

Answer 1

由于某些原因，当我尝试用您的代码创建一个测试表时，这些都显示为无效的JSON。但是，看看JSON，似乎有一个嵌套的数组。因此，您需要查看平面：https://docs.snowflake.com/en/sql-reference/functions/flatten.html

像这样的东西会给你的代码列表，他们的名字和类型，从那里你可以过滤的类型。

select f.value:type, f.value:name
FROM ##input p,
       lateral flatten(input => data, path => 'Codes') f