PostgreSQL 9.4如何实现json_strip_nulls等效功能？

Got it, since json_strip_nulls() didn't land until PostgreSQL 9.5, you need a solid workaround for 9.4 to clean up those null-valued keys cluttering your JSON column. Let's break this down based on whether you only need to handle top-level keys or nested structures too.

Top-Level Null Key Removal #

If you're only dealing with null values at the top level of your JSON objects, you can use a simple combination of json_each() to expand the JSON into key-value pairs, filter out the nulls, then aggregate back into a JSON object with json_object_agg().

Here's a sample query:

SELECT 
  id, 
  json_object_agg(key, value) AS stripped_json
FROM 
  your_table, 
  json_each(your_json_column)
WHERE 
  value IS NOT NULL
GROUP BY 
  id;

This will take each row's JSON column, split it into individual key-value pairs, drop any pairs where the value is null, then recombine the remaining pairs into a clean JSON object.

Handling Nested JSON Structures #

If your JSON has nested objects or arrays with null-valued keys, the top-level method won't reach those. For this, you'll need a recursive PL/pgSQL function that traverses every level of the JSON structure and removes null values wherever they appear.

Create this function in your database:

CREATE OR REPLACE FUNCTION json_strip_nulls_94(json_val json)
RETURNS json AS $$
BEGIN
  -- Return null immediately if input is null
  IF json_val IS NULL THEN
    RETURN NULL;
  END IF;

  -- Process JSON objects: filter out null values and recurse on remaining values
  IF json_typeof(json_val) = 'object' THEN
    RETURN (
      SELECT json_object_agg(key, json_strip_nulls_94(value))
      FROM json_each(json_val)
      WHERE value IS NOT NULL AND json_typeof(value) != 'null'
    );
  -- Process JSON arrays: recurse on each element in the array
  ELSIF json_typeof(json_val) = 'array' THEN
    RETURN (
      SELECT json_agg(json_strip_nulls_94(element))
      FROM json_array_elements(json_val) AS element
    );
  -- For scalar values (string, number, boolean), return as-is
  ELSE
    RETURN json_val;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

How to Use It #

Once the function is created, you can call it directly on your JSON column:

SELECT json_strip_nulls_94(your_json_column) AS cleaned_json FROM your_table;

This will recursively strip out every null-valued key from objects at any depth, and process arrays to clean up their nested elements too.

Key Notes #

• The function is marked IMMUTABLE, which means it can be used to create indexes if you need to speed up repeated queries on cleaned JSON.
• Converting between json and jsonb in 9.4 won't remove null values, as you noticed—this workaround explicitly targets and filters out those null key-value pairs.
• If you're dealing with large datasets, test the recursive function on a subset first to gauge performance, though it's efficient enough for most typical use cases.

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