JSON compact

Snippets

JSON compact

Works with PostgreSQL

9.4+

Written in

plpgsql

Depends on

Nothing

In some cases you may want to remove all unnecessary noise from a JSON document, in particular if you plan to store it in your DB. This snippet does exactly this task.

CREATE OR REPLACE FUNCTION public.json_compact(p_json JSON,
                                               p_step INTEGER DEFAULT 0)
RETURNS JSON
AS $$
DECLARE
  v_type TEXT;
  v_text TEXT := '';
  v_indent INTEGER;
  v_key TEXT;
  v_object JSON;
  v_count INTEGER;
BEGIN
  p_step := coalesce(p_step, 0);
  -- Object or array?
  v_type := json_typeof(p_json);
  
  IF v_type = 'object' THEN
    -- Start object
    v_text := '{';
    SELECT count(*) - 1 INTO v_count
    FROM json_object_keys(p_json);
    -- go through keys, add them and recurse over value
    FOR v_key IN (SELECT json_object_keys(p_json))
    LOOP
      v_text := v_text || to_json(v_key)::TEXT || ':' || public.json_compact(p_json->v_key, p_step + 1);
      IF v_count > 0 THEN
        v_text := v_text || ',';
        v_count := v_count - 1;
      END IF;
      --v_text := v_text || E'\n';
    END LOOP;
    -- Close object
    v_text := v_text || '}';
  ELSIF v_type = 'array' THEN
    -- Start array
    v_text := '[';
    v_count := json_array_length(p_json) - 1;
    -- go through elements and add them through recursion
    FOR v_object IN (SELECT json_array_elements(p_json))
    LOOP
      v_text := v_text || public.json_compact(v_object, p_step + 1);
      IF v_count > 0 THEN
        v_text := v_text || ',';
        v_count := v_count - 1;
      END IF;
      --v_text := v_text || E'\n';
    END LOOP;
    -- Close array
    v_text := v_text || ']';
  ELSE -- A simple value
    v_text := v_text || p_json::TEXT;
  END IF;
  IF p_step > 0 THEN RETURN v_text;
  ELSE RETURN v_text::JSON;
  END IF;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION public.json_compact(p_json JSON,
                                        p_step INTEGER)
IS $$Return a JSON document in a compact JSON suitable for storing in the DB.
Parameters:
* p_json: The JSON document to be compacted.
* p_step: Optional: This parameter is used for recursion in the document structure. You are not supposed to use it.$$;