PostgreSQL如何基于jsonb字段创建text[]类型的存储生成列

错误原因 #

PostgreSQL规定生成列的表达式不允许包含子查询，你所用的ARRAY(SELECT ...)写法属于子查询范畴，因此触发报错。同时生成列要求使用的函数必须为不可变（IMMUTABLE）类型，返回集合的函数也不能直接嵌套在数组构造器中使用。

正确实现方式 #

推荐通过自定义不可变函数的方式实现，兼容性最好，适配所有支持生成列的PostgreSQL版本：

1. 首先创建转换函数：

CREATE OR REPLACE FUNCTION extract_body_values(input_json jsonb)
RETURNS text[] LANGUAGE sql IMMUTABLE AS $$
SELECT COALESCE(ARRAY_AGG(elem ->> 'value'), '{}'::text[])
FROM jsonb_array_elements(COALESCE(input_json -> 'body', '[]'::jsonb)) elem;
$$;

上述函数内置了空值兼容处理，当data字段不存在body键、body为null或非数组格式时，会返回空数组避免报错。

2. 再添加生成列：

ALTER TABLE 你的表名 
ADD COLUMN tags text[] GENERATED ALWAYS AS (extract_body_values(data)) STORED;

PostgreSQL 14+ 简化写法 #

如果你的数据库版本为PostgreSQL 14及以上，可以直接用JSON路径查询实现，无需自定义函数：

ALTER TABLE 你的表名
ADD COLUMN tags text[] GENERATED ALWAYS AS (
    trim(both '[]' from jsonb_path_query_array(data, '$.body[*].value')::text)::text[]
) STORED;

注：简化写法依赖JSON数组转文本的格式特性，如果value字段内容包含方括号可能出现转义异常，复杂场景优先使用自定义函数方案。

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