JSON 格式化

代码片段

JSON 格式化

适用于 PostgreSQL

9.4+

用

plpgsql

依赖于

无

PostgreSQL 9.5 版本引入了 JSONB 以及 jsonb_pretty 函数。因此，可以通过先将 JSON 文档转换为 JSONB，然后使用内置的 jsonb_pretty 函数来轻松地格式化它。

但是，如果需要保持文档中键的顺序，或者存在重复的键，则可以使用此函数。

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.$$;

用法

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)

可以将输出格式设置为 unaligned 来移除表格装饰。