JSON pretty

From PostgreSQL wiki
Jump to navigationJump to search

Snippets

JSON pretty

Works with PostgreSQL

9.4+

Written in

plpgsql

Depends on

Nothing


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.