PostgreSQL中基于JSONB列生成计算列时过滤特定purpose值的实现方案

很简单，你只需要调整你的tags函数，在提取body数组元素时加上对purpose字段的过滤，就能排除掉那些purpose为commenting的项了。

修改后的函数代码 #

CREATE OR REPLACE FUNCTION tags (data jsonb) 
RETURNS text[] 
AS $CODE$
BEGIN
  RETURN ARRAY (
    SELECT elem ->> 'value'
    FROM jsonb_array_elements(data -> 'body') AS elem
    WHERE elem ->> 'purpose' <> 'commenting' -- 新增过滤条件
  );
END $CODE$ LANGUAGE plpgsql IMMUTABLE;

关键修改说明 #

• 我把原来的嵌套查询拆分成了带别名的子查询，这样能方便地引用每个数组元素的purpose属性
• 新增的WHERE子句会直接过滤掉所有purpose等于commenting的元素，只保留其他项的value内容

验证效果 #

针对你插入的这条测试数据：

{
  "id": "#b709f4fa-fdce-423a-b760-5f0fed7d3393",
  "body": [
    { "type": "TextualBody", "value": "Triangle", "purpose": "shapes" },
    { "type": "TextualBody", "value": "this is a comment", "purpose": "commenting" }
  ]
}

修改函数后，tags字段会生成['Triangle']，完全排除了那条评论的内容。

另外，这个过滤逻辑依然是纯基于输入jsonb数据的计算，没有依赖外部状态，所以可以保持函数的IMMUTABLE属性，这对生成列的性能和正确性至关重要。

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