如何用单条JsonPath从PostgreSQL中检索多个JSON对象值

示例JSON结构 #

{
    "root": {
        "child-1": {
            "grandchild-1": "value1",
            "grandchild-2": "value2",
            "grandchild-3": "value3"
        },
        "child-2": {
            "grandchild-4": "value4",
            "grandchild-5": {
                "grandgrandchild-1": "value5"
            }
        }
    }
}

问题描述 #

如何编写单条PostgreSQL查询，通过一条JsonPath语句直接返回多个目标值（例如["value3", "value5"]）？

当前尝试的查询方式为多次调用jsonb_path_query：

select jsonb_path_query(resp_json, '$.*.child-1.grandchild-3'),
       jsonb_path_query(resp_json, '$.*.child-2.grandchild-5.grandgrandchild-1')
from req_resp_body;

该方法存在局限：每查询一个值就要调用一次函数，当需要查询的目标值数量不确定时完全不可用。

注意事项 #

• 使用*代替明确的根节点名称（如示例中的root）是刻意要求，因为查询使用者可能不知道实际的根节点名称。

解决方案 #

可以通过JsonPath的路径合并语法，在一条路径表达式中同时指定多个目标节点，结合对应函数返回所需格式的结果：

1. 返回JSON数组格式 #

使用jsonb_path_query_array函数将所有目标值封装为一个数组：

select jsonb_path_query_array(resp_json, '$.*.child-1.grandchild-3 || $.*.child-2.grandchild-5.grandgrandchild-1')
from req_resp_body;

执行后会直接返回["value3", "value5"]。

2. 返回多行单列格式 #

如果希望每个目标值单独占一行，替换为jsonb_path_query函数即可：

select jsonb_path_query(resp_json, '$.*.child-1.grandchild-3 || $.*.child-2.grandchild-5.grandgrandchild-1')
from req_resp_body;

语法说明 #

• ||是PostgreSQL JsonPath中的路径合并运算符，用于将多个路径的查询结果合并为一个集合；
• 路径中的$.*会匹配根节点下的所有顶层子节点，满足"不知道实际根节点名称"的需求。

灵活匹配拓展（可选） #

如果目标节点名称固定但层级不确定，可以用递归下降运算符**简化路径，自动匹配任意层级下的目标节点：

select jsonb_path_query_array(resp_json, '$..grandchild-3 || $..grandgrandchild-1')
from req_resp_body;

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