PostgreSQL JSONB：如何通过路径获取对应键名？

PostgreSQL 14原生确实没有直接返回JSON路径最后一个键名的函数，不过可以通过自定义PL/pgSQL函数实现需求——提取路径的最后一个键，并验证该路径是否存在对应的JSONB节点，存在则返回键名，否则返回null。

自定义函数实现 #

CREATE OR REPLACE FUNCTION jsonb_get_last_key(jsonb_data jsonb, json_path text)
RETURNS text AS $$
DECLARE
    last_key text;
BEGIN
    -- 提取多级路径的最后一个键（如$.address.street → street）
    last_key := regexp_replace(json_path, '^\$\.(.*)\.(.*)$', '\2');
    -- 处理单级路径的情况（如$.name → name）
    IF last_key = json_path THEN
        last_key := regexp_replace(json_path, '^\$\.(.*)$', '\1');
    END IF;
    
    -- 验证路径是否存在对应的JSONB节点
    IF jsonb_path_exists(jsonb_data, json_path) THEN
        RETURN last_key;
    ELSE
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

测试示例 #

使用你提供的示例JSONB数据进行测试：

WITH sample_data AS (
    SELECT '{
        "name": "John Doe",
        "age": 30,
        "address": {
            "street": "123 Main St",
            "city": "Anytown",
            "state": "CA",
            "zip": "12345",
            "data": {"a": "b", "c": "d"}
        }
    }'::jsonb AS data
)
-- 测试存在的路径
SELECT jsonb_get_last_key(data, '$.address.street') AS result;
-- 输出：street

-- 测试不存在的路径
SELECT jsonb_get_last_key(data, '$.address.non_existent') AS result;
-- 输出：null

-- 测试单级路径
SELECT jsonb_get_last_key(data, '$.name') AS result;
-- 输出：name

注意事项 #

• 该函数仅处理对象键路径，不支持包含数组索引的路径（如$.address.data[0]这类格式），如果需要支持数组场景，可进一步扩展正则表达式逻辑。
• 确保传入的json_path是符合PostgreSQL JSON路径语法的合法字符串。

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