如何根据ID筛选PostgreSQL jsonb列中嵌套数组的子集

你需要返回完整的jsonb列内容，但仅保留suggestions数组中包含指定item id的条目。以下是可行的实现方案：

核心查询（筛选id='foo'的情况） #

SELECT 
  jsonb_set(
    my_column, 
    '{suggestions}', 
    (
      SELECT jsonb_agg(suggestion)
      FROM jsonb_array_elements(my_column->'suggestions') AS suggestion
      WHERE EXISTS (
        SELECT 1 
        FROM jsonb_array_elements(suggestion->'items') AS item
        WHERE item->>'id' = 'foo'
      )
    )
  ) AS filtered_column
FROM my_table
WHERE EXISTS (
  SELECT 1 
  FROM jsonb_array_elements(my_column->'suggestions') AS suggestion
  WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(suggestion->'items') AS item
    WHERE item->>'id' = 'foo'
  )
);

代码说明 #

1. jsonb_set：替换原json中的suggestions字段，将过滤后的数组填充进去，其他字段（如targets）保持不变，确保返回完整的结构。
2. 子查询生成过滤后的数组：
   • jsonb_array_elements(my_column->'suggestions')：拆分suggestions数组为单个元素
   • WHERE EXISTS：筛选出items数组中包含指定id的suggestion元素
   • jsonb_agg(suggestion)：将筛选后的元素重新组合为json数组
3. 外层WHERE条件：仅返回确实存在符合条件的suggestion的行，避免返回suggestions为空数组的行（不需要可移除）。

简化方案（使用jsonb_path_query_array） #

如果喜欢用JSON路径表达式，可采用以下更简洁的写法：

SELECT 
  jsonb_set(
    my_column,
    '{suggestions}',
    jsonb_path_query_array(
      my_column->'suggestions',
      '$[*] ? (exists (@.items[*] ? (@.id == "foo")))'
    )
  ) AS filtered_column
FROM my_table
WHERE jsonb_path_exists(
  my_column,
  '$.suggestions[*].items[*] ? (@.id == "foo")'
);

路径表达式解释：

• $[*]：遍历suggestions数组的每个元素
• ? (exists (@.items[*] ? (@.id == "foo")))：判断当前suggestion的items数组中是否存在id等于"foo"的元素，符合条件则保留

动态参数适配 #

如果需要动态指定筛选的id，可使用参数化查询（以PostgreSQL的$1参数为例）：

SELECT 
  jsonb_set(
    my_column, 
    '{suggestions}', 
    (
      SELECT jsonb_agg(suggestion)
      FROM jsonb_array_elements(my_column->'suggestions') AS suggestion
      WHERE EXISTS (
        SELECT 1 
        FROM jsonb_array_elements(suggestion->'items') AS item
        WHERE item->>'id' = $1
      )
    )
  ) AS filtered_column
FROM my_table
WHERE EXISTS (
  SELECT 1 
  FROM jsonb_array_elements(my_column->'suggestions') AS suggestion
  WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(suggestion->'items') AS item
    WHERE item->>'id' = $1
  )
);

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