Postgresql12 json function新特性jsonpath查询介绍

简介

postgresql 12 里更新了json function文档，新增了带jsonpath查询语句参数的三个新方法：

• jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
• jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
• jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

它能在很大程度上解决复杂、确定结构json内部的查询难题，比起展开另起子查询，极大的简化了json查询步骤。

场景

在数据库表里存在一个json字段存了结构比较复杂但有规律的信息的时候，把存在json里的信息作条件查询。

建表语句：

create table code_system
(
    "Id"              uuid                  not null
        constraint "PK_code_system"
            primary key,
    "LastUpdateTime"  timestamp             not null,
    "Name"            text,
    "Title"           text,
    "UseContext"      jsonb
);

INSERT INTO code_system ("Id", "Name", "Title", "UseContext", "LastUpdateTime") VALUES ('11261838-6cb6-4413-9d90-3bc9c867eec0', 'CV04.50.005', 'ABO血型代码表', '[{"code": {"code": "DataDomain", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "数据域", "userSelected": true}, "valueCodeableConcept": {"text": "", "coding": [{"code": "blood_abo", "system": "http://mdm.com/CodeSystem/mdmDataDomain", "display": "ABO血型", "userSelected": true}, {"code": "basic_info", "system": "http://mdm.com/CodeSystem/mdmDataDomain", "display": "人的基本信息"}]}}, {"code": {"code": "StandardType", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "标准类型", "userSelected": true}, "valueCodeableConcept": {"text": "", "coding": [{"code": "3", "system": "http://mdm.com/CodeSystem/mdm-code-level", "display": "行业标准", "userSelected": true}]}}, {"code": {"code": "UseSubject", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "用途", "userSelected": true}, "valueCodeableConcept": {"coding": [{"code": "02", "system": "http://mdm.com/CodeSystem/mdm-code-system-usage", "display": "互联互通标准", "userSelected": true}]}}]', '2019-11-16 13:56:06.319739');
INSERT INTO code_system ("Id", "Name", "Title", "UseContext", "LastUpdateTime") VALUES ('c2059186-78ba-46d6-8dde-0b2b93550a8b', 'GB/T2261.1-2003', '性别代码', '[{"code": {"code": "DataDomain", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "数据域", "userSelected": true}, "valueCodeableConcept": {"text": "", "coding": [{"code": "sex", "system": "http://mdm.com/CodeSystem/mdmDataDomain", "display": "性别", "userSelected": true}, {"code": "basic_info", "system": "http://mdm.com/CodeSystem/mdmDataDomain", "display": "人的基本信息"}]}}, {"code": {"code": "StandardType", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "标准类型", "userSelected": true}, "valueCodeableConcept": {"text": "", "coding": [{"code": "2", "system": "http://mdm.com/CodeSystem/mdm-code-level", "display": "国家标准", "userSelected": true}]}}, {"code": {"code": "UseSubject", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "用途", "userSelected": true}, "valueCodeableConcept": {"coding": [{"code": "02", "system": "http://mdm.com/CodeSystem/mdm-code-system-usage", "display": "互联互通标准", "userSelected": true}, {"code": "01", "system": "http://mdm.com/CodeSystem/mdm-code-system-usage", "display": "院内标准", "userSelected": true}]}}]', '2019-11-16 13:56:42.920166');
INSERT INTO code_system ("Id", "Name", "Title", "UseContext", "LastUpdateTime") VALUES ('bde410df-1843-4d57-a600-417f59dd3121', 'DE02.01', '人口学及社会经济学特征', '[{"code": {"code": "DataDomain", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "数据域", "userSelected": true}, "valueCodeableConcept": {"text": "", "coding": [{"code": "data_meta_1", "system": "http://mdm.com/CodeSystem/mdmDataSetDataDomain", "display": "数据元", "userSelected": true}, {"code": "data_meta", "system": "http://mdm.com/CodeSystem/mdmDataSetDataDomain", "display": "数据元"}]}}, {"code": {"code": "dataSet", "system": "http://mdm.com/CodeSystem/mdmCodeSystemType", "display": "数据集"}, "valueCodeableConcept": {"coding": [{"code": "dataSet", "system": "http://mdm.com/CodeSystem/mdmCodeSystemType", "display": "数据集"}]}}, {"code": {"code": "StandardType", "system": "http://mdm.com/CodeSystem/mdmFilterType", "display": "标准类型", "userSelected": true}, "valueCodeableConcept": {"text": "", "coding": [{"code": "7", "system": "http://mdm.com/CodeSystem/mdm-code-level", "display": "其他标准", "userSelected": true}]}}]', '2019-11-16 13:54:18.894593');

示例1 jsonb_path_exists

查出UseContext字段json数组中object中含有code的code为“StandardType”，且valueCodeableConcept中coding数组里object含有display为“国家标准”的行，且打上判断标记。

SELECT "Id", "LastUpdateTime", "Name", "Title",
jsonb_path_exists("UseContext", '$[*] ? (@.code.code == "StandardType" && @.valueCodeableConcept.coding[*].display == "国家标准")')
FROM code_system;

解释第2行：
jsonb_path_exists调用了两个参数："UseContext"表示查询到的code_system里的字段作为整个被查询的jsonb传入；第二个参数就是jsonpath查询语句了。

jsonpath解读：

1. $表示json的根元素，[]表示此元素是数组元素，*是通配符表示任意下标位置，合为：\$[*] 根数组任意下标的元素。
2. ?表示以此为起点开始查询，即把数组打开每个object作为查询起点，它用@代替开数组后的object。
3. @.code.code查询该object里code字段下code字段的值全等于"StandardType"，且@.valueCodeableConcept.coding[*].display查询该object里valueCodeableConcept字段下coding数组中任意object的display全等于“国家标准”，得出是否符合查询的布尔值。

结果：

Id	LastUpdateTime	Name	Title	jsonb_path_exists
11261838-6cb6-4413-9d90-3bc9c867eec0	2019-11-16 13:56:06.319739	CV04.50.005	ABO血型代码表	false
c2059186-78ba-46d6-8dde-0b2b93550a8b	2019-11-16 13:56:42.920166	GB/T2261.1-2003	性别代码	true
bde410df-1843-4d57-a600-417f59dd3121	2019-11-16 13:54:18.894593	DE02.01	人口学及社会经济学特征	false

注意：它可以当where语句的值来选择过滤条目。

示例2 jsonb_path_match

查出UseContext字段json数组中有object中含有code的code为“DataDomain”，且json数组中有object含有valueCodeableConcept的coding数组里object含有code为“basic_info”的行，且打上判断标记。

SELECT "Id", "LastUpdateTime", "Name", "Title",
       jsonb_path_match("UseContext",'$[*].code.code == $codeValue && $[*].valueCodeableConcept.coding[*].code == $codingValue',
           '{"codeValue":"DataDomain","codingValue":"basic_info"}')
FROM code_system;

解释第三行：
jsonb_path_match调用的第三个参数为前一个jsonpath里存在$标注的变量的情况下，变量的赋值语句。
第三个参数在三个方法里是通用的。

jsonpath解读：

1. \$[*].code.code == $codeValue 表示根数组中有object的code的code值全等于变量codeValue。
2. \$[*].valueCodeableConcept.coding[*].code == $codingValue 表示根数组中有object的valueCodeableConcept的coding数组有object的code全等于codingValue。
3. {"codeValue":"DataDomain","codingValue":"basic_info"} jsonpath中codeValue值为“DataDomain”，codingValue值为“basic_info”。

结果：

Id	LastUpdateTime	Name	Title	jsonb_path_match
11261838-6cb6-4413-9d90-3bc9c867eec0	2019-11-16 13:56:06.319739	CV04.50.005	ABO血型代码表	true
c2059186-78ba-46d6-8dde-0b2b93550a8b	2019-11-16 13:56:42.920166	GB/T2261.1-2003	性别代码	true
bde410df-1843-4d57-a600-417f59dd3121	2019-11-16 13:54:18.894593	DE02.01	人口学及社会经济学特征	false

---

注意：jsonb_path_match和jsonb_path_exists的jsonpath是有区别的，区别在于match不支持@?语法，只能从根节点往下写查询判断。这也导致了两者的查询结果在带有数组下标的通配符 * 的时候，解析理解的作用域会不一样。例如：

SELECT "Id", "LastUpdateTime", "Name", "Title",
       jsonb_path_exists("UseContext", '$[*] ? (@.code.code == "DataDomain" && @.valueCodeableConcept.coding[*].display == "国家标准")')
FROM code_system;

SELECT "Id", "LastUpdateTime", "Name", "Title",
       jsonb_path_match("UseContext",'$[*].code.code == "DataDomain" && $[*].valueCodeableConcept.coding[*].display == "国家标准"')
FROM code_system;

这两个看似相近的jsonb_path_exists和jsonb_path_match写法的语义其实是不一样的，当然执行结果也不一样。
如果非要在jsonb_path_match中使用带数组下标通配符 * 的查询，为保证准确性，建议在jsonpath中用exist(...)函数将@@语法转换为@?语法

示例3 jsonb_path_query

查出UseContext字段json数组中有object中含有code的code为“UseSubject”的条目，并将被找到的object中的valueCodeableConcept的coding数组展开把其中每个object的display字段作为UseSubject展示到最终结果后，以表明每个条目拥有的UseSubject。

WITH usesubject AS(
    SELECT "Id", "LastUpdateTime", "Name", "Title",
           jsonb_path_query("UseContext",'$[*] ? (@.code.code == "UseSubject")')->'valueCodeableConcept'->'coding' coding
    FROM code_system
)
SELECT "Id", "LastUpdateTime", "Name", "Title",jsonb_array_elements(coding)->>'display' "UseSubject"  FROM usesubject us;

解释第三行：
jsonb_path_query在查找到含UseSubject的条目后，将其相关的valueCodeableConcept的coding数组作为coding保存。

解释第五行：
jsonb_array_elements展开coding数组为json object结果集之后，取该object里的display字段值为UseSubject最终结果。

结果：

Id	LastUpdateTime	Name	Title	UseSubject
11261838-6cb6-4413-9d90-3bc9c867eec0	2019-11-16 13:56:06.319739	CV04.50.005	ABO血型代码表	互联互通标准
c2059186-78ba-46d6-8dde-0b2b93550a8b	2019-11-16 13:56:42.920166	GB/T2261.1-2003	性别代码	互联互通标准
c2059186-78ba-46d6-8dde-0b2b93550a8b	2019-11-16 13:56:42.920166	GB/T2261.1-2003	性别代码	院内标准

参考资料

PostgreSQL 12 - Table 9.47. JSON Processing Functions