PostgreSQL json path属性规范查询及按属性值检索数组元素咨询

嗨，这个问题问得很到位！先帮你厘清一个容易搞混的点：jsonb_set里的path text[]参数，和PostgreSQL里的JSON Path表达式是两码事——前者只是个简单的键/索引路径数组（比如'{users, 0, name}'），只能定位固定位置的元素，没法直接写条件匹配元素属性；但咱们可以通过两种方式实现你要的需求：

方式一：适配旧版本PostgreSQL（<12）——先找索引再用jsonb_set #

如果你的PostgreSQL版本低于12，没有jsonb_set_path函数，那就得先找到目标元素在数组中的索引，再把索引拼到jsonb_set的path参数里。

举个实际例子：假设你有一张表user_data，其中data字段是这样的JSONB数据：

{
  "users": [
    {"id": 1, "name": "Alice"},
    {"id": 2, "name": "Bob"},
    {"id": 3, "name": "Charlie"}
  ]
}

现在要修改id=2的用户的name为"Robert"，步骤如下：

1. 先通过jsonb_array_elements配合WITH ORDINALITY找到目标元素的索引（注意：ORDINALITY返回的是从1开始的序号，而JSON数组的索引是0-based，所以要减1）：

SELECT ordinality - 1 AS arr_idx
FROM user_data, jsonb_array_elements(data->'users') WITH ORDINALITY
WHERE value->>'id' = '2';

2. 把这个索引拼到jsonb_set的path里，执行更新：

UPDATE user_data
SET data = jsonb_set(
  data,
  '{users, ' || arr_idx || ', name}'::text[],
  '"Robert"'::jsonb
)
FROM (
  SELECT id, ordinality - 1 AS arr_idx
  FROM user_data, jsonb_array_elements(data->'users') WITH ORDINALITY
  WHERE value->>'id' = '2'
) AS target_elem
WHERE user_data.id = target_elem.id;

方式二：PostgreSQL 12+ 直接用jsonb_set_path + JSON Path表达式 #

从PostgreSQL 12开始，新增了jsonb_set_path函数，它直接支持完整的JSON Path语法，包括基于属性值的过滤条件，用起来更简洁！

还是上面的需求，一行SQL就能搞定：

UPDATE user_data
SET data = jsonb_set_path(
  data,
  '$.users[*] ? (@.id == 2).name', -- JSON Path表达式：匹配users数组中id=2的元素的name字段
  '"Robert"'::jsonb
);

这里的JSON Path表达式解释一下：

• $.users[*]：遍历users数组的所有元素
• ? (@.id == 2)：过滤出id等于2的元素
• .name：定位到该元素的name字段

总结 #

• 如果你用的是PostgreSQL 12及以上版本，优先用jsonb_set_path配合JSON Path表达式，直接实现基于属性值检索数组元素的需求；
• 旧版本则需要先通过jsonb_array_elements+序数找到元素索引，再传递给jsonb_set使用。

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