PostgreSQL中如何将JSONB嵌套UUID数组转换为同结构字段对象数组

使用嵌套JSONB数组存储动态报表模板UI结构时，数组内仅保存各字段对应的UUID值，需要在完全保留原有数组嵌套结构的前提下，将其中的UUID替换为对应字段的完整元数据对象。
技术栈为PostgreSQL 12，后端服务采用Hasura。

结构示例 #

• report_template表中fields字段（JSONB类型）存储的原始结构：

[[UUID, UUID], [UUID, UUID], UUID]

• 期望转换结果：

[[
    {"id": UUID, "type": "INPUT", "label": "last Name" },
    {"id": UUID, "type": "NUMBER", "label": "Age"}
  ], [
    {"id": UUID, "type": "DATE", "label": "Date"},
    {"id": UUID, "type": "INPUT", "label": "first Name"}
  ],
  {"id": UUID, "type": "INPUT", "label": "middle name"}
]

关联表结构 #

• 字段元数据表report_template_fields：存储字段属性，每条记录包含3个字段：
  • id：UUID类型，字段唯一标识
  • name：字符串类型，字段名称
  • type：字符串类型，字段类型
• 报表模板表report_template：存储模板信息，其中fields字段为JSONB类型，保存嵌套UUID数组形式的模板结构。

通过PostgreSQL递归自定义函数实现任意嵌套层级的JSONB遍历替换，无需修改原有数组结构，函数可直接被Hasura识别为计算字段使用。

递归替换函数定义 #

CREATE OR REPLACE FUNCTION replace_field_uuid(input_json jsonb)
RETURNS jsonb AS $$
DECLARE
    result jsonb;
    field_meta jsonb;
BEGIN
    -- 遍历数组元素，递归处理嵌套结构
    IF jsonb_typeof(input_json) = 'array' THEN
        SELECT jsonb_agg(replace_field_uuid(elem))
        INTO result
        FROM jsonb_array_elements(input_json) AS elem;
        RETURN result;
    -- 匹配到UUID字符串时，关联查询元数据生成对象
    ELSIF jsonb_typeof(input_json) = 'string' THEN
        SELECT jsonb_build_object(
            'id', rtf.id,
            'type', rtf.type,
            'label', rtf.name
        )
        INTO field_meta
        FROM report_template_fields rtf
        WHERE rtf.id = (input_json #>> '{}')::uuid;
        -- 未匹配到元数据时默认返回原UUID值，可按需调整为null或抛出异常
        RETURN COALESCE(field_meta, input_json);
    -- 非数组、非UUID字符串的异常值直接返回
    ELSE
        RETURN input_json;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

调用方式 #

查询报表模板时直接调用函数，即可得到替换完成的带元数据结构：

SELECT 
    id,
    -- 其他模板字段
    replace_field_uuid(fields) AS fields_with_meta
FROM report_template;

Hasura适配配置 #

1. 在Hasura控制台跟踪上述自定义函数
2. 将函数配置为report_template表的计算字段
3. 配置完成后即可在GraphQL请求中直接查询返回带完整元数据的嵌套fields结构，无需额外开发后端接口

说明：函数标记为IMMUTABLE后PostgreSQL会自动缓存相同输入的计算结果，常规报表模板场景下无明显性能损耗；如果模板嵌套层级极深，可在函数定义中添加SET max_recursion_depth = 10000调整递归深度阈值。

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