如何从PostgreSQL中正确提取特定JSONB键的值并过滤空值

我来帮你搞定这个PostgreSQL JSONB的问题哈～

一、先解决「过滤出仅包含键'a'的行」需求 #

要精准获取包含'a'键的3行数据，你可以用PostgreSQL专门为JSONB设计的?操作符——它能直接判断JSONB的顶层是否存在指定键，完美过滤掉没有'a'的行：

-- 获取原始的包含'a'键的JSONB数据
SELECT jdoc FROM test WHERE jdoc ? 'a';

-- 或者只提取每行中'a'键对应的内容
SELECT jdoc->'a' AS a_content FROM test WHERE jdoc ? 'a';

执行这两个查询都会返回3行结果，不会出现那行无'a'键的null值。

二、修复你报错的聚合查询 #

你原来的jsonb_object_agg查询有两个核心问题：一是jsonb_each不能直接传入子查询，得用LATERAL JOIN关联每行的JSONB数据；二是jdoc->'a'不一定是对象类型（比如你的数据里有字符串、数组），直接用jsonb_each会报错，因为这个函数只支持处理JSONB对象。

根据你的需求分两种场景处理：

场景1：仅聚合'a'值为对象的键值对

如果你想把所有'a'值是对象的行中的键值对聚合起来，可以这么写：

SELECT jsonb_object_agg(sub.key, sub.value)
FROM test
-- 横向关联每行的'a'值，仅处理'a'存在且为对象的情况
JOIN LATERAL jsonb_each(test.jdoc->'a') AS sub
ON test.jdoc ? 'a' AND jsonb_typeof(test.jdoc->'a') = 'object';

这个查询会返回{"b": "foo"}，因为只有第一行的'a'是对象类型。

场景2：聚合所有'a'对应的内容（含字符串、数组）

如果你想把所有包含'a'键的行的'a'值聚合到一个对象里（为避免键重复，这里结合id做了区分）：

SELECT jsonb_object_agg('a_' || id, jdoc->'a')
FROM test
WHERE jdoc ? 'a';

执行后会得到类似这样的结果：

{"a_1": {"b": "foo"}, "a_2": "test", "a_3": [2, 3, 4]}

小补充 #

• jdoc ? 'a'和jsonb_exists(jdoc, 'a')效果一致，前者是更简洁的写法
• jsonb_typeof()函数可以帮你判断JSONB的类型（object、string、array等），避免处理非预期类型导致报错

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