PostgreSQL按优先级获取参数并实现父级继承的SQL方案问询

问题 #

我有一个PostgreSQL表params，包含param_scope、param_name、param_value三列。param_scope的格式如下：
/mainGroups/{familyId}/groups/{groupName}/groups/{subGroupName}/parameters
或/mainGroups/{familyId}/groups/{groupName}/parameters，支持无限层级的子组。

我需要构建一个JS函数来生成PostgreSQL查询语句，返回高优先级的组参数：组层级越深优先级越高，深层参数会覆盖浅层的同名参数，未被深层覆盖的父级参数需要被继承。函数的传入参数为family、groupId、precedenceList。

示例场景 #

给定precedenceList：

[
  { group: 'group3', precedence: 1 },
  { group: 'group2', precedence: 2 },
  { group: 'group1', precedence: 3 }
]

family: balderstone，groupId: group2，数据库中的数据如下：

[
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/parameters",
    "param_name": "param1",
    "param_value": "A"
  },
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/groups/group2/parameters",
    "param_name": "param1",
    "param_value": "B"
  },
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/parameters",
    "param_name": "param2",
    "param_value": "false"
  },
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/groups/group2/parameters",
    "param_name": "param2",
    "param_value": "true"
  },
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/parameters",
    "param_name": "param3",
    "param_value": "4000"
  },
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/groups/group2/groups/group3/parameters",
    "param_name": "param3",
    "param_value": "6000"
  }
]

期望结果 #

[
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/groups/group2/parameters",
    "param_name": "param1",
    "param_value": "B"
  },
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/groups/group2/parameters",
    "param_name": "param2",
    "param_value": "true"
  },
  {
    "param_scope": "/mainGroups/balderstone/groups/group1/parameters",
    "param_name": "param3",
    "param_value": "4000"
  }
]

（注：param3从group2的父级group1继承，因为group2本身没有定义param3，而group3的优先级低于group2，所以不采用group3的param3）

我现在只能获取group2的参数，无法实现继承逻辑。尝试的SQL如下：

SELECT 
    MAX(LEVEL) AS level,
    MAX(param_scope),
    param_name,
--  MAX(value) AS value <-- currently picks wrong value
FROM 
(
    SELECT *, 1 AS LEVEL FROM params WHERE param_scope = "/parameters"

    UNION ALL

    SELECT *, 2 AS LEVEL FROM params WHERE param_scope = "/mainGroups/balderstone/parameters"

    UNION ALL

    SELECT *, 3 AS LEVEL FROM params WHERE param_scope = "/mainGroups/balderstone/groups/group1/parameters"

    UNION ALL

    SELECT *, 4 AS LEVEL FROM params WHERE param_scope = "/mainGroups/balderstone/groups/group1/groups/group2/parameters"

) 
AS paramsPerLevel
GROUP BY param_name

但这个语句无法返回继承的参数，请问如何用PostgreSQL实现该需求？

解决方案 #

要实现层级覆盖+继承的逻辑，核心是先筛选出目标路径及其所有父级路径的参数，再对每个参数名保留优先级最高（层级最深）的记录。具体实现如下：

1. 构建路径层级CTE #

首先定义目标路径及其所有父级路径的层级关系，层级数值越大代表优先级越高。

2. 用窗口函数筛选最高优先级参数 #

使用ROW_NUMBER()窗口函数按参数名分组，按层级降序排序，确保每个参数名只保留最高优先级的记录。

完整SQL示例（适配场景） #

WITH path_levels AS (
    -- 动态生成目标路径及其父级路径的层级（JS生成时替换为动态内容）
    SELECT '/mainGroups/balderstone/groups/group1/groups/group2/parameters' AS param_scope, 4 AS level
    UNION ALL
    SELECT '/mainGroups/balderstone/groups/group1/parameters' AS param_scope, 3 AS level
    UNION ALL
    SELECT '/mainGroups/balderstone/parameters' AS param_scope, 2 AS level
    UNION ALL
    SELECT '/parameters' AS param_scope, 1 AS level
),
ranked_params AS (
    SELECT 
        p.param_scope,
        p.param_name,
        p.param_value,
        pl.level,
        -- 按参数名分组，层级降序排名，第1条即为最高优先级
        ROW_NUMBER() OVER (PARTITION BY p.param_name ORDER BY pl.level DESC) AS rn
    FROM params p
    JOIN path_levels pl ON p.param_scope = pl.param_scope
)
-- 只保留每个参数名的最高优先级记录
SELECT param_scope, param_name, param_value
FROM ranked_params
WHERE rn = 1
ORDER BY param_name;

JS动态生成SQL的要点 #

在JS函数中：

• 根据precedenceList找到目标groupId的位置，生成从根到该groupId的完整路径链。
• 为每个路径分配递增的层级数值（层级越深数值越大）。
• 将这些路径拼接成path_levels中的UNION语句。

逻辑优势 #

• 精准关联层级与参数值，避免了原方案中MAX(param_value)取错值的问题。
• 自动保留父级未被覆盖的参数，实现继承逻辑。
• 结构清晰，易于通过JS动态适配不同的family和groupId参数。

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