Postgres查询单行数据时忽略NULL列并返回JSON的实现方案咨询

服务端最优实现方案 #

Postgres 原生提供了jsonb_strip_nulls函数，可以完美匹配你的需求，实现逻辑为先把整行数据转为JSON对象，再自动删除所有值为NULL的键，不需要针对不同行的字段差异做额外适配。

假设你的营养成分表名为product_nutrition，查询指定产品的语句示例：

SELECT jsonb_strip_nulls(to_jsonb(t)) AS nutrition_info
FROM product_nutrition t
WHERE product_id = '你要查询的产品ID';

补充说明：

• 如果使用的是json类型而非jsonb，替换为json_strip_nulls即可，更推荐使用jsonb类型，查询和后续操作性能更优
• 如果需要排除主键、创建时间等不需要返回的固定字段，可以直接用-运算符删除指定键，示例：

-- 排除不需要返回的product_id、create_time字段
SELECT jsonb_strip_nulls(to_jsonb(t) - 'product_id' - 'create_time') AS nutrition_info
FROM product_nutrition t
WHERE product_id = 'xxx';

前端过滤方案可行性和性能影响 #

• 方案本身可行：单条数据即使有150列，整体数据量一般只有几KB，网络传输和前端遍历过滤的性能开销几乎可以忽略，不会有明显感知的卡顿
• 不推荐的原因：服务端仅需一行SQL就能完成过滤，既可以减少无效数据传输，也能避免不同前端业务重复写过滤逻辑，维护成本更低

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