PostgreSQL递归函数返回层级JSON而非字符串的实现方法

你的问题出在函数返回类型定义为varchar，且递归调用时将JSON结果当作字符串处理，导致多层嵌套时出现大量转义字符。以下是修改步骤和最终代码：

修改要点 #

• 将函数返回类型从varchar改为json
• 内部变量RESULT的类型同步改为json
• 递归调用时直接返回JSON类型值，避免字符串转义
• 处理无节点返回的情况，用COALESCE确保返回空数组而非null，避免INTO STRICT报错

修改后的函数代码 #

CREATE OR REPLACE FUNCTION HIERARCHAL_JSON_FN(P_id bigint) 
RETURNS json AS $body$
DECLARE
   RESULT json;
BEGIN
   SELECT COALESCE(
      json_agg(
          json_build_object(
              'ID', ID,
              'PARENT_ID', PARENT_ID,
              'NAME', NAME,
              'CHILD', public.HIERARCHAL_JSON_FN(CAST(id AS bigint))
          )
      ), '[]'::json
   )
   INTO RESULT
   FROM HIERARCHAL_JSON
   WHERE coalesce(PARENT_ID, 0) = coalesce(P_id, 0);
   
   RETURN RESULT;
END;
$body$
LANGUAGE PLPGSQL;

调用测试 #

执行以下语句即可获取无转义的层级JSON：

SELECT HIERARCHAL_JSON_FN(null);

返回结果示例：

[
  {
    "ID": 1,
    "PARENT_ID": null,
    "NAME": "x",
    "CHILD": [
      {
        "ID": 2,
        "PARENT_ID": 1,
        "NAME": "y",
        "CHILD": [
          {
            "ID": 3,
            "PARENT_ID": 2,
            "NAME": "z",
            "CHILD": []
          }
        ]
      }
    ]
  }
]

原理说明 #

原函数返回字符串类型，递归时json_build_object会将子节点的字符串结果当作普通文本处理，自动添加转义符以符合JSON字符串格式。修改为返回json类型后，json_build_object会直接将子节点的JSON值嵌套到父节点中，无需转义，最终输出原生的层级JSON结构。

内容的提问来源于stack exchange，提问作者Farida