如何从PostgreSQL的jsonb_path_query获取完整JSON路径？

你需要通过jsonb_set更新JSONB列，但该函数要求传入完整的JSON路径。当JSONB包含数组结构时，如何获取匹配特定条件的元素的完整路径？

例如，现有如下JSONB数据：

{
  "Laptop": {
    "brand": "Dell",
    "price": 1200,
    "specs": [
      {
        "name": "CPU",
        "Brand": "Intel"
      },
      {
        "name": "GPU",
        "Brand": "Nvdia"
      },
      {
        "name": "RAM",
        "Brand": "Kingston"
      }
    ]
  }
}

你希望执行类似如下的查询，获取匹配name为"CPU"的元素的完整路径：

SELECT <full_path> 
  FROM your_table 
 WHERE jsonb_path_query_first(col, '$.Laptop.specs[*].name')::text = '"CPU"';

期望返回结果可以是$.Laptop.specs[0].name格式，或者更便于使用的{Laptop,specs,0,name}数组格式。

方法1：获取JSON路径格式结果 #

使用jsonb_path_query并指定returning: path参数，可以直接返回匹配元素的完整JSON路径：

SELECT trim(jsonb_path_query(col, '$.Laptop.specs[*] ? (@.name == "CPU")', '{"returning": "path"}')::text, '"') AS full_json_path
FROM your_table;

该查询会返回$.Laptop.specs[0].name，刚好可以直接传入jsonb_set作为路径参数。

方法2：获取PostgreSQL数组格式结果 #

如果需要返回{Laptop,specs,0,name}这种数组格式的路径，可以通过拆分JSON路径字符串实现：

SELECT string_to_array(trim(jsonb_path_query(col, '$.Laptop.specs[*] ? (@.name == "CPU")', '{"returning": "path"}')::text, '"$."'), '.')::text[] AS path_array
FROM your_table;

这个查询会将JSON路径拆分转换为PostgreSQL文本数组，结果为{Laptop,specs,0,name}。

带条件过滤的完整查询 #

如果需要先过滤出符合条件的行，再提取路径，可以用CTE来实现：

WITH filtered_rows AS (
  SELECT 
    col,
    jsonb_path_query(col, '$.Laptop.specs[*] ? (@.name == "CPU")', '{"returning": "path"}')::text AS raw_path
  FROM your_table
)
SELECT
  trim(raw_path, '"') AS full_json_path,
  string_to_array(trim(raw_path, '"$."'), '.')::text[] AS path_array
FROM filtered_rows
WHERE raw_path IS NOT NULL;

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