IRIS 2024.1新特性 - JSON_TABLE | InterSystems Developer Community |

文章

Qiao Peng · 三月 16 阅读大约需 10 分钟

#InterSystems IRIS #InterSystems IRIS for Health

IRIS 2024.1已经发布，它引入了诸多新特性，其中之一是JSON_TABLE。

数据表达和交换中，JSON已经是日益主流的存在。在之前的IRIS版本中，可以轻易将JSON数据以对象解析并保存到IRIS，也可以将IRIS数据使用SQL、对象等多种方式输出为JSON。对于得到的JSON序列化的数据，如果我们想通过SQL去解析，甚至进行检索和查询，就可以利用JSON_TABLE这个新特性。

对于一些大规模的JSON序列化数据，例如从FHIR服务器查询获得的FHIR资源Bundle，里面包含了大量数据。例如下面的FHIR查询结果，后面的示例以这个的复杂的JSON作为用例：

{
    "resourceType": "Bundle",
    "id": "cf34e50f-e350-11ee-821c-005056b6b4c5",
    "type": "searchset",
    "timestamp": "2024-03-16T04:51:10Z",
    "total": 2,
    "link": [
        {
            "relation": "self",
            "url": "http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient"
        }
    ],
    "entry": [
        {
            "fullUrl": "http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient/1",
            "resource": {
                "resourceType": "Patient",
                "text": {
                    "status": "generated"
                },
                "active": true,
                "name": [
                    {
                        "text": "张三",
                        "family": "张",
                        "given": [
                            "三"
                        ]
                    }
                ],
                "telecom": [
                    {
                        "system": "phone",
                        "value": "010-1234567",
                        "use": "work"
                    }
                ],
                "gender": "female",
                "birthDate": "1955-10-25",
                "address": [
                    {
                        "use": "home",
                        "line": [
                            "北京市东城区某某小区10号楼9单999室"
                        ]
                    }
                ],
                "id": "1",
                "meta": {
                    "lastUpdated": "2023-12-11T05:54:08Z",
                    "versionId": "1"
                }
            },
            "search": {
                "mode": "match"
            }
        },
        {
            "fullUrl": "http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient/example",
            "resource": {
                "resourceType": "Patient",
                "id": "example",
                "text": {
                    "status": "generated"
                    },
                "identifier": [
                    {
                        "use": "usual",
                        "type": {
                            "coding": [
                                {
                                    "system": "http://terminology.hl7.org/CodeSystem/v2-0203",
                                    "code": "MR"
                                }
                            ]
                        },
                        "system": "urn:oid:1.2.36.146.595.217.0.1",
                        "value": "12345",
                        "period": {
                            "start": "2001-05-06"
                        },
                        "assigner": {
                            "display": "北京大学人民医院"
                        }
                    }
                ],
                "active": true,
                "name": [
                    {
                        "use": "official",
                        "family": "王",
                        "given": [
                            "王"
                        ]
                    },
                    {
                        "use": "usual",
                        "given": [
                            "锤哥"
                        ]
                    },
                    {
                        "use": "maiden",
                        "family": "刘",
                        "given": [
                            "老虎"
                        ],
                        "period": {
                            "end": "2002"
                        }
                    }
                ],
                "telecom": [
                    {
                        "use": "home"
                    },
                    {
                        "system": "phone",
                        "value": "010-12345",
                        "use": "work",
                        "rank": 1
                    },
                    {
                        "system": "phone",
                        "value": "010-1234545",
                        "use": "mobile",
                        "rank": 2
                    },
                    {
                        "system": "phone",
                        "value": "010-1255345",
                        "use": "old",
                        "period": {
                            "end": "2014"
                        }
                    }
                ],
                "gender": "male",
                "birthDate": "1974-12-25",
                "_birthDate": {
                    "extension": [
                        {
                            "url": "http://hl7.org/fhir/StructureDefinition/patient-birthTime",
                            "valueDateTime": "1974-12-25T14:35:45-05:00"
                        }
                    ]
                },
                "deceasedBoolean": false,
                "address": [
                    {
                        "use": "home",
                        "type": "both",
                        "text": "朝阳北路110号",
                        "line": [
                            "朝阳大悦城"
                        ],
                        "city": "北京",
                        "district": "朝阳区",
                        "state": "北京",
                        "postalCode": "100110",
                        "period": {
                            "start": "1974-12-25"
                        }
                    }
                ],
                "contact": [
                    {
                        "relationship": [
                            {
                                "coding": [
                                    {
                                        "system": "http://terminology.hl7.org/CodeSystem/v2-0131",
                                        "code": "N"
                                    }
                                ]
                            }
                        ],
                        "name": {
                            "family": "牛",
                            "_family": {
                                "extension": [
                                    {
                                        "url": "http://hl7.org/fhir/StructureDefinition/humanname-own-prefix",
                                        "valueString": "VV"
                                    }
                                ]
                            },
                            "given": [
                                "大能"
                            ]
                        },
                        "telecom": [
                            {
                                "system": "phone",
                                "value": "13009172345"
                            }
                        ],
                        "address": {
                            "use": "home",
                            "type": "both",
                            "line": [
                                "南口子镇"
                            ],
                            "city": "济南",
                            "district": "历下区",
                            "state": "山东",
                            "postalCode": "1234444",
                            "period": {
                                "start": "1974-12-25"
                            }
                        },
                        "gender": "female",
                        "period": {
                            "start": "2012"
                        }
                    }
                ],
                "managingOrganization": {
                    "reference": "Organization/1"
                },
                "meta": {
                    "lastUpdated": "2023-12-11T08:51:21Z",
                    "versionId": "1"
                }
            },
            "search": {
                "mode": "match"
            }
        }
    ]
}

通过动态对象实例化这样的数据再进一步解析有些笨拙。如果想快速解析它们，并获得患者的姓名和性别信息，甚至想通过一个SQL语句将这些信息保存到数据库，那么JSON_TABLE就是最好的选择。

JSON_TABLE语法

JSON_TABLE是一个将JSON数据或数据源投射为SQL临时表的技术。它的数据源可以是JSON字符串、返回JSON的函数、或者是保存JSON数据的表字段。

JSON字符串示例：'[{"number":"two","arr":[1,2,3]}, {"number":"three","arr":[55,66]}, {"number":"four"}]'
返回JSON字符串的函数示例：%Net.GetJson('http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient')
保存JSON字符串的列：JsonValues.numbers

JSON_TABLE使用JPL(JSON Path Language)语法对JSON数据定位和解析，创建JSON_TABLE的语法是：

JSON_TABLE( json-value, json-path col-mapping )

其中：

json-value：json数据源
json-path: JPL表达式，确定从JSON数据源的哪个部分提取用于投射的JSON数据
col-mapping: 建立字段声明，说明投射关系，包括投射出的字段名称、数据类型、JSON路径的JPL表达式

例如对FHIR服务器进行FHIR API的查询获得所有患者资源，并找到男性患者的姓名和性别，我们可以使用下面的SQL语句：

json-value: 这里通过RESTful服务获得FHIR JSON数据，所以可以用IRIS提供SQL函数%Net.GetJson，从目标RESTful服务地址http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient获得数据，写作：%Net.GetJson('http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient')

json-path：因为要从返回的JSON字符串的content.entry开始解析，所以用$.content.entry。这里$是JPL语法中从头开始解析的意思。

col-mapping: 我们要返回2个字段，gender和name，SQL数据类型都是VARCHAR(100)，JPL路径分别是$.resource.gender和$.resource.name。用关键字COLUMNS将2个字段的声明括在一起即可。注意，COLUMNS关键字和上一步json-path之间没有逗号！

至于限定性别为男性的SQL条件，因为我们已经声明了gender字段，使用标准WHERE子句限定即可。整个SQL语句就是：

  SELECT * 
    FROM JSON_TABLE(
             %Net.GetJson('http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient'),
             '$.content.entry'
             COLUMNS(gender VARCHAR(100) PATH '$.resource.gender',
 		         name VARCHAR(100) PATH '$.resource.name'
            ))
   WHERE gender='male'

JSON_TABLE示例

下面提供几种常见的JSON_TABLE示例。

表字段作为JSON源的示例：

例如表Example.JsonValues的字段numbers是一个JSON数据，可以如下处理：

SELECT number
  FROM Example.JsonValues,
       JSON_TABLE(JsonValues.numbers,
                  '$'
                  COLUMNS (number INTEGER PATH '$.number')
                 )

REST服务作为JSON源的示例：

使用IRIS的SQL函数%Net.GetJson，例如：

SELECT *
  FROM JSON_TABLE(%Net.GetJson('http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient'),
                  '$.content.entry'
                  COLUMNS(gender VARCHAR(100) PATH '$.resource.gender',
                          name VARCHAR(100) PATH '$.resource.name'
                          )
                 )

过滤器示例：

在解析JSON数据时，可以用过滤器JPL语法对JSON事先过滤。例如返回的FHIR JSON结果集中只想处理其中的Condition资源，可以使用

$.content.entry[*]?(@.resource.resourceType=="Condition")进行过滤：

SELECT * 
  FROM JSON_TABLE(%Net.GetJson('http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient/1/$everything'),
                  '$.content.entry[*]?(@.resource.resourceType=="Condition")'
                  COLUMNS(type VARCHAR(100) PATH '$.resource.resourceType',
                          code VARCHAR(100) PATH '$.resource.code.coding.get(0).display' 
                         )
                 )

JSON数组的处理示例：

如果要处理JSON数组，可以使用JPL的[]语法，或IRIS扩展的get()函数进行处理。例如，FHIR患者资源的name是一个数组，name下的given也是数组，我们只想返回数组中的第一个元素，可以用get(0)：

SELECT *
  FROM JSON_TABLE(%Net.GetJson('http://172.19.85.68/csp/healthshare/hcc/fhir/r5/Patient'),
                  '$.content.entry[*]?(@.resource.resourceType=="Patient")'
                  COLUMNS(gender VARCHAR(100) PATH '$.resource.gender',
                          family VARCHAR(100) PATH '$.resource.name.get(0).family',
                           given VARCHAR(100) PATH '$.resource.name.get(0).given.get(0)'
                         )
                 )