PostgreSQL 16：能否用生成存储列聚合嵌套JSONB数组的去重州缩写？

现有PostgreSQL 16.x版本的数据表，包含jsonb类型列data，该列存储的JSON对象数组格式如下：

[ { "states": ["AZ", "CA"], ... }, { "states": ["NY","CO"], ... }, ... ]

需求是将所有JSON对象中的states数组聚合为一个去重的州缩写列表，生成名为states的存储生成列（示例结果：["AZ", "CA", "NY", "CO", ...]）。

目前已有查询方法，但逻辑复杂且包含子查询，无法用于定义存储生成列：

select jsonb_path_query_array(
  ( select jsonb_agg(b) from
    (select distinct jsonb_array_elements(a) as state from jsonb_array_elements(
       jsonb_path_query_array(data, '$[*].states')
    ) as a) as b
  ), '$[*].state') as states
from myTable

用户希望仅通过列内数据转换实现，不使用select子查询或join lateral。

可以利用PostgreSQL 16的JSON路径查询特性，通过单个jsonb_path_query_array函数实现去重聚合，完全符合列内转换要求，可直接用于定义存储生成列：

ALTER TABLE myTable
ADD COLUMN states jsonb GENERATED ALWAYS AS (
  jsonb_path_query_array(data, '$[*].states[*]'::jsonpath, '{"distinct": true}')
) STORED;

关键说明 #

1. 路径表达式：$[*].states[*] 先遍历data数组中的每个对象，再展开每个对象的states数组，将所有州缩写扁平化为一个列表。
2. 去重参数：第三个参数'{"distinct": true}' 启用JSON路径查询的去重功能，自动剔除重复的州缩写。
3. 兼容性：该实现完全基于列内函数计算，无任何子查询或横向连接，满足存储生成列的定义规则。

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