PostgreSQL：如何通过两个键值使用XPath查询XML字段

方式1：用xpath_exists直接匹配节点条件 #

可以通过XPath表达式直接定位到同时满足project_id和state_id的assignment节点，判断是否存在这样的节点：

WHERE xpath_exists(
    '/assignments/assignment[project_id/text()="6512779208625374885" and state_id/text()="1"]',
    t.assignments
)

这个方法适合仅需要判断记录是否符合条件的场景，语法简洁。

方式2：通过unnest拆分XML节点后过滤 #

先把所有assignment节点拆出来，再逐个检查每个节点下的两个字段：

WHERE EXISTS (
    SELECT 1
    FROM unnest(xpath('/assignments/assignment', t.assignments)) AS assignment_node
    WHERE xpath('project_id/text()', assignment_node)::text = '6512779208625374885'
      AND xpath('state_id/text()', assignment_node)::text = '1'
)

这种方式更灵活，适合需要对单个节点做更多复杂判断的场景。

方式3：用XMLTABLE解析并过滤（PostgreSQL 12+） #

如果需要提取匹配节点的其他字段（比如start_date），推荐使用XMLTABLE将XML结构转为关系型数据后再过滤：

SELECT t.*, x.*
FROM your_table t,
     XMLTABLE(
         '/assignments/assignment'
         PASSING t.assignments
         COLUMNS
             project_id TEXT PATH 'project_id',
             state_id INT PATH 'state_id',
             start_date TIMESTAMPTZ PATH 'start_date'
     ) AS x
WHERE x.project_id = '6512779208625374885'
  AND x.state_id = 1;

这个方法不仅能过滤条件，还能直接获取XML中的具体字段值，便于后续处理。

注意：不要直接分别提取project_id和state_id的数组后做匹配，因为数组的顺序不一定能保证和原XML中的节点一一对应，容易出现错误匹配的情况。

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