PostgreSQL JsonPath识别数组时筛选条件失效问题及解决方案

更新：用户a_horse_with_no_name的评论及关联问题明确了问题原因：过滤器中@符号引用数组时会自动展开数组，导致无法直接应用筛选条件，下文为问题详情及最终解决方案。

测试环境准备 #

测试表创建语句 #

CREATE SEQUENCE user_id_seq;
CREATE TABLE jstbl (id int NOT NULL DEFAULT nextval('user_id_seq'), jsval jsonb);
ALTER SEQUENCE user_id_seq OWNED BY jstbl.id;

插入测试数据 #

测试JSON为应用UI布局结构，由多个容器组成，每个容器包含1个或多个组件：

insert into
    jstbl (jsval)
    values('{
  "content":[
          {
            "id": "c1",
            "_type":"container",
            "data": [
              {
                "_type":"widget",
                "id":"WIDGET 1"
              }
            ]
          },
          {
            "id": "c2",
            "_type":"container",
            "data":[
              {
                "_type":"widget",
                "id":"WIDGET 2"
              },
              {
                "_type":"widget",
                "id":"WIDGET 3"
              }
            ]
          }
  ]
}');

问题复现 #

content第一个元素是id为c1的对象，其data字段为数组类型，可通过以下语句验证：

select
       jsonb_path_query(
           jsval,
           '$.content[0].data.type()')
from jstbl;

以上语句返回结果为：array。

但将类型判断放入筛选条件时查询失效，返回空结果：

select
       jsonb_path_query(
           jsval,
           '$.content[0].* ?(@.type() == "array")')
from jstbl;

问题根因 #

过滤器中@符号引用数组时会自动展开数组，相当于直接判断数组内部元素的类型，而非判断字段本身是否为数组类型，因此筛选条件无法匹配到预期结果。

解决方案 #

• 调整路径写法，在过滤器中完成.*展开，让筛选条件正常生效，示例路径为'$.content[0] ?(@.*.type() == "array")'
• 使用strict模式，也可实现预期筛选效果

内容的提问来源于stack exchange，提问作者GrumpyRodriguez