PostgreSQL如何合并多个JSONB数组为去重的单一数组？

可以通过展开数组元素→去重→重新聚合的步骤实现，无需自定义函数，直接用PostgreSQL内置函数完成：

完整查询语句 #

WITH
  products AS (
      SELECT * FROM configured_products() p
      WHERE p.is_active
  ),
  products_countries as (
      SELECT countries FROM products
      WHERE products.group_ids @> to_jsonb(121) -- test purpose
      GROUP BY products.countries
  )
SELECT jsonb_agg(DISTINCT country) AS countries
FROM products_countries, jsonb_array_elements_text(countries) AS country;

关键步骤说明 #

1. 展开数组元素：使用jsonb_array_elements_text()函数，将每一行的jsonb数组拆分为单独的文本行（比如把["FR", "LU"]拆成两行FR和LU）
2. 去重：通过DISTINCT关键字过滤重复的国家代码（比如原结果中重复的FR）
3. 重新聚合：用jsonb_agg()将去重后的单个国家代码重新组合成一个jsonb数组，得到最终的扁平化无重复数组

为什么直接用jsonb_agg()会得到嵌套结构？ #

如果直接对products_countries中的countries字段使用jsonb_agg(countries)，本质是把整个数组作为元素进行聚合，最终会生成[["BE"], ["FR"], ["FR", "LU"]]这样的嵌套结构，而先展开再聚合则能得到扁平化的目标数组。

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