PostgreSQL如何删除jsonb字段中所有值为null的元素并保留原排序

PostgreSQL内置的jsonb_strip_nulls仅支持删除顶层的null值，不会处理嵌套结构内的null，所以你之前需要通过类型转换、字符串替换的方式绕路实现，最稳定简洁的方案是自定义递归处理函数：

CREATE OR REPLACE FUNCTION jsonb_strip_nulls_recursive(input jsonb)
RETURNS jsonb AS $$
BEGIN
  -- 处理JSON对象类型
  IF jsonb_typeof(input) = 'object' THEN
    RETURN (
      SELECT jsonb_object_agg(key, jsonb_strip_nulls_recursive(value))
      FROM jsonb_each(input)
      WHERE value IS NOT NULL AND value != 'null'::jsonb
    );
  -- 如果你不需要处理JSON数组可以删除以下数组处理分支
  ELSIF jsonb_typeof(input) = 'array' THEN
    RETURN (
      SELECT jsonb_agg(jsonb_strip_nulls_recursive(element))
      FROM jsonb_array_elements(input)
      WHERE element IS NOT NULL AND element != 'null'::jsonb
    );
  -- 基础类型直接返回
  ELSE
    RETURN input;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

函数创建完成后，你可以直接调用实现需求，完全不需要复杂的类型转换和字符串替换，也不会出现字符串替换可能导致的转义错误：

SELECT 
  node_id,
  jsonb_build_object('addInfo', jsonb_strip_nulls_recursive(tags->'addInfo')) AS cleaned_tags
FROM points p
WHERE p.tags IS NOT NULL
  AND p.tags ->> 'amenity' = 'fuel';

这个方案处理后的结果完全符合你的预期：所有嵌套层级的null值都会被清除，全为null的子对象会保留为空对象。

注意jsonb是二进制优化存储类型，默认不会保留键的插入顺序，会按照内部哈希规则重排键的顺序。如果你必须保留operating、payment、fueltype的原有排序，需要使用json类型而非jsonb类型：

1. 你可以选择将表的tags字段类型修改为json
2. 适配json类型的递归清理函数如下，增加了顺序保留逻辑：

CREATE OR REPLACE FUNCTION json_strip_nulls_recursive(input json)
RETURNS json AS $$
BEGIN
  IF json_typeof(input) = 'object' THEN
    RETURN (
      SELECT json_object_agg(key, json_strip_nulls_recursive(value) ORDER BY ordinality)
      FROM json_each(input) WITH ORDINALITY
      WHERE value IS NOT NULL AND value != 'null'::json
    );
  ELSIF json_typeof(input) = 'array' THEN
    RETURN (
      SELECT json_agg(json_strip_nulls_recursive(element) ORDER BY ordinality)
      FROM json_array_elements(input) WITH ORDINALITY
      WHERE element IS NOT NULL AND element != 'null'::json
    );
  ELSE
    RETURN input;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

调用该函数处理后的json数据会完全保留原有的键排序。

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