JSON pretty

Version 9.5 introduced JSONB and with it jsonb_pretty. So it is very easy to prettify a JSON document casting it first
to JSONB and using the built-in function jsonb_pretty.
However, if you need to keep the order of the keys in your document or if you have
repeated keys you may want to use this function instead.
CREATE OR REPLACE FUNCTION public.json_pretty(p_json JSON,
 p_indent_size INTEGER DEFAULT 4,
 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
 v_indent := coalesce(p_indent_size, 4);
 p_step := coalesce(p_step, 0);
 -- Object or array?
 v_type := json_typeof(p_json);
 
 IF v_type = 'object' THEN
 -- Start object
 v_text := E'{\n';
 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 || repeat(' ', v_indent * (p_step + 1)) || to_json(v_key)::TEXT || ': ' || public.json_pretty(p_json->v_key, p_indent_size, 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 || repeat(' ', (v_indent * p_step)) || '}';
 ELSIF v_type = 'array' THEN
 -- Start array
 v_text := E'[\n';
 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 || repeat(' ', v_indent * (p_step + 1)) || public.json_pretty(v_object, p_indent_size, 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 || repeat(' ', (v_indent * p_step)) || ']';
 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_pretty(p_json JSON,
 p_indent_size INTEGER,
 p_step INTEGER)
IS $$Return a JSON document as a formatted JSON suitable for better reading from humans.
Parameters:
* p_json: The JSON document to be formatted.
* p_indent_size: Optional: The number of blanks to be used for indenting. Default is 4.
* p_step: Optional: This parameter is used for recursion in the document structure. You are not supposed to use it. If you use it, your formatted document will be shifted to the right by an amount corresponding to p_indent_size * p_step.$$;
=== Usage ===
db=> select * from public.json_pretty('{"a":"2","c":{"b":2, "b":4, "F":{"U1":14,"U2":"Hallo Welt"}},"array":["a","c",2,{"F":"obj in array"}, [8, 9, 10, "undici"]],"m":7567,"N":{"xyz":"uid"}}');
 json_pretty
---------------------------------
 { +
 "a": "2", +
 "c": { +
 "b": 4, +
 "b": 4, +
 "F": { +
 "U1": 14, +
 "U2": "Hallo Welt" +
 } +
 }, +
 "array": [ +
 "a", +
 "c", +
 2, +
 { +
 "F": "obj in array"+
 }, +
 [ +
 8, +
 9, +
 10, +
 "undici" +
 ] +
 ], +
 "m": 7567, +
 "N": { +
 "xyz": "uid" +
 } +
 }
(1 row)
You may want to set the output format to unaligned to remove the the table decoration.
Category: Snippets