PostgreSQL 14中如何获取匹配指定JsonPath过滤器的所有路径？

我使用PostgreSQL 14，需要找出所有与给定JsonPath过滤器匹配的路径，目标是定位所有包含required验证器条件的A.B路径。

输入示例 #

{
  "A": [
    {
      "B": [
        [
          {
            "name": "id",
            "conditions": [
              {
                "validator": "nonnull"
              }
            ]
          },
          {
            "name": "x",
            "conditions": [
              {
                "validator": "required"
              }
            ]
          },
          {
            "name": "y",
            "rules": []
          }
        ],
        [
          {
            "name": "z",
            "conditions": [
              {
                "validator": "required"
              }
            ]
          }
        ]
      ]
    }
  ]
}

JsonPath过滤器 #

用于匹配包含required验证器条件的A.B路径：

$.A.B[*].conditions ? (@.validator == "required")

解决方案 #

可以使用PostgreSQL的jsonb_path_query函数结合WITH PATH子句，同时获取匹配的元素及其路径，再将路径转换为数组格式：

假设存储JSON数据的表为test，JSON字段为data，执行以下查询：

SELECT jsonb_path_to_array(path) AS matched_path
FROM test,
     jsonb_path_query(data, '$.A.B[*].conditions ? (@.validator == "required")' WITH PATH) AS jp(val, path);

预期输出 #

{A,0,B,0,1,conditions,0}
{A,0,B,1,0,conditions,0}

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