PostgreSQL XML转JSON函数优化：适配dbt并移除冗余字段

我在dbt宏与模型中使用网上找到的xml_to_json PL/pgSQL函数实现XML转JSON，但生成的JSON包含attr、text、childs等冗余结构，不符合预期格式。输入指定XML后，当前输出带有冗余嵌套字段，而我需要更简洁的层级结构。尝试直接移除构建语句中的冗余字段未成功，现寻求优化该函数的方案。

原函数代码 #

create or replace function xml_to_json(p_xml xml) returns jsonb as $$
declare
  result json;
  root text;
  childs jsonb;
  attr jsonb;
  txt text;
begin
  -- Get root element name
  select (xpath('name(/*)', p_xml))[1]::text into root;

  -- Process child nodes
  select json_agg(xml_to_json(z))
  from unnest(xpath('/'||root||'/*', p_xml)) with ordinality as c(z,i)
  into childs;

  -- Read attributes
  select jsonb_agg(jsonb_build_object((xpath('name(/'||root||'/@*['||i||'])', p_xml))[1]::text, v))
  from unnest(xpath('/'||root||'/@*', p_xml)::text[]) with ordinality as a(v,i) 
  into attr;

  -- Read text
  select (xpath('/'||root||'/text()', p_xml))[1]::text into txt;

  -- Build object
  result := jsonb_build_object(root, jsonb_build_object('attr', attr, 'text', txt, 'childs', childs));
  return result;
end $$ language plpgsql immutable;

输入XML示例 #

<root>
    <a>a1</a>
    <b>
        <c>b1</c>
    </b>
</root>

当前输出JSON #

{
  "root": {
    "attr": null,
    "text": "\n    ",
    "childs": [
      {
        "a": {
          "attr": null,
          "text": "a1",
          "childs": null
        }
      },
      {
        "b": {
          "attr": null,
          "text": "\n        ",
          "childs": [
            {
              "c": {
                "attr": null,
                "text": "b1",
                "childs": null
              }
            }
          ]
        }
      }
    ]
  }
}

预期输出JSON #

{
  "root":  [
      {"a":  "a1"},
      {"b": [{"c": "b1"}]}
    ]
}

优化后的函数实现 #

create or replace function xml_to_json(p_xml xml) returns jsonb as $$
declare
  root text;
  child_nodes xml[];
  node_content jsonb;
  text_content text;
  result jsonb;
begin
  -- 获取根节点名称
  select (xpath('name(/*)', p_xml))[1]::text into root;

  -- 获取所有子节点
  select array_agg(node) from unnest(xpath('/' || root || '/*', p_xml)) as node into child_nodes;

  -- 处理文本内容，过滤空白字符
  select trim(coalesce((xpath('/' || root || '/text()', p_xml))[1]::text, '')) into text_content;

  if child_nodes is not null and array_length(child_nodes, 1) > 0 then
    -- 如果有子节点，递归处理每个子节点并聚合为数组
    select jsonb_agg(xml_to_json(node)) into node_content from unnest(child_nodes) as node;
  elsif text_content != '' then
    -- 如果没有子节点但有有效文本，直接返回文本值
    node_content := to_jsonb(text_content);
  else
    -- 空节点返回null（可根据需求调整）
    node_content := null;
  end if;

  -- 构建最终结构：根节点对应子节点数组或文本值
  result := jsonb_build_object(root, node_content);
  return result;
end $$ language plpgsql immutable;

优化思路说明 #

• 过滤无效文本：使用trim()去除节点文本中的换行、空格等无意义空白字符，避免无效内容混入结果
• 简化递归逻辑：
  • 节点存在子节点时，递归处理子节点并聚合为JSON数组
  • 节点仅含有效文本时，直接返回文本值，不再嵌套冗余字段
  • 完全移除attr、text、childs等中间层级，直接构建XML与JSON的层级映射
• 空值智能处理：自动忽略无内容的节点属性和空白文本，避免输出冗余null字段

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