JSON pretty
From PostgreSQL wiki
Jump to navigationJump to search
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.