PostgreSQL：从JSONB生成数组时排除NULL元素的实现方法

这个场景我太熟悉了——直接用array[]构造数组时，只要JSONB里缺某个字段，转成int后就会出现NULL，导致数组里混着空值。不过有几种实用的方法可以搞定这个需求：

方法1：用array_remove()直接清理NULL（最简单） #

PostgreSQL内置的array_remove()函数可以直接把数组里指定的元素（这里就是NULL）移除，写法非常直观：

SELECT array_remove(
    array[
        ('{"tech_id": 4}'::jsonb->>'admin_id')::int,
        ('{"tech_id": 4}'::jsonb->>'tech_id')::int
    ],
    NULL
);

执行后会直接返回{4}，完美去掉了NULL值。这种方法适合字段数量少、逻辑简单的场景，一行就能搞定。

方法2：展开数组过滤后再聚合（灵活扩展） #

如果之后需要加更多过滤条件（比如值要大于某个数），可以先用unnest()把数组拆成单行数据，过滤掉NULL后再用array_agg()重新拼成数组：

SELECT array_agg(val)
FROM unnest(
    array[
        ('{"tech_id": 4}'::jsonb->>'admin_id')::int,
        ('{"tech_id": 4}'::jsonb->>'tech_id')::int
    ]
) AS t(val)
WHERE val IS NOT NULL;

这种方式的扩展性很强，比如要排除小于3的值，直接在WHERE里加val > 3就行。

方法3：提前判断字段存在性（优雅适配多字段） #

如果需要提取的字段很多，重复写JSONB表达式会很繁琐，这时候可以先列出所有要提取的key，用?操作符判断JSONB里是否存在该key，只提取存在的字段：

SELECT ARRAY(
    SELECT (jsonb_field->>key)::int
    FROM (
        VALUES ('admin_id'), ('tech_id') -- 这里列出所有要提取的字段名
    ) AS keys(key)
    WHERE jsonb_field ? key
)
FROM (SELECT '{"tech_id": 4}'::jsonb AS jsonb_field) AS t;

这种写法不仅避免了NULL的产生，还让代码更整洁——要加新字段的话，直接在VALUES里加一行就行，不用修改其他逻辑。

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