将JSONB键转换为列：动态字段映射SQL实现方案问询

1. 动态生成透视查询SQL #

因为配置字段是动态更新的，静态SQL无法提前指定列名，必须通过动态SQL实现目标格式输出，以下是两种可行方式：

方式一：用PL/pgSQL函数封装动态查询 #

创建函数自动生成并执行透视逻辑：

CREATE OR REPLACE FUNCTION get_user_profile()
RETURNS SETOF record AS $$
DECLARE
    cols text;
BEGIN
    -- 生成所有目标列的CASE语句，自动适配ProfileFields的动态配置
    SELECT string_agg(
        format('MAX(CASE WHEN pf.profilefieldid = %L THEN uv.value END) AS %I', pf.profilefieldid, pf.value),
        ', '
    ) INTO cols
    FROM "ProfileFields" pf;

    -- 拼接并执行最终SQL
    RETURN QUERY EXECUTE format(
        'SELECT u.id, u.name, %s
         FROM "users" u
         LEFT JOIN jsonb_each_text(u.information) uv(key, value)
             ON CAST(uv.key AS INTEGER) IN (SELECT profilefieldid FROM "ProfileFields")
         LEFT JOIN "ProfileFields" pf ON CAST(uv.key AS INTEGER) = pf.profilefieldid
         GROUP BY u.id, u.name
         ORDER BY u.id',
        cols
    );
END;
$$ LANGUAGE plpgsql;

调用时需指定返回结构（或提前创建视图）：

SELECT * FROM get_user_profile() AS t(id integer, name text, Company text, DateOfBirth text, ProfileLink text);

方式二：手动生成动态SQL（适合一次性查询） #

先执行以下语句生成可直接运行的透视SQL：

SELECT format(
    'SELECT u.id, u.name, %s
     FROM "users" u
     LEFT JOIN jsonb_each_text(u.information) uv(key, value)
         ON CAST(uv.key AS INTEGER) IN (SELECT profilefieldid FROM "ProfileFields")
     LEFT JOIN "ProfileFields" pf ON CAST(uv.key AS INTEGER) = pf.profilefieldid
     GROUP BY u.id, u.name
     ORDER BY u.id',
    string_agg(
        format('MAX(CASE WHEN pf.profilefieldid = %L THEN uv.value END) AS %I', pf.profilefieldid, pf.value),
        ', '
    )
) AS dynamic_sql
FROM "ProfileFields" pf;

将输出的dynamic_sql内容复制执行，即可得到目标格式的结果。

2. 修复你当前的SQL问题 #

你的SQL存在两个核心错误：表名大小写不匹配（PostgreSQL双引号包裹的表名需严格一致）、子查询逻辑错误导致返回多行。修改后的聚合查询（适合不需要拆分列的场景）：

SELECT
    u.id,
    u.name,
    string_agg(
        concat(pf.value, ':', uv.value),
        ', '
    ) AS information
FROM "users" u
LEFT JOIN jsonb_each_text(u.information) uv(key, value)
    ON CAST(uv.key AS INTEGER) IN (SELECT profilefieldid FROM "ProfileFields")
LEFT JOIN "ProfileFields" pf ON CAST(uv.key AS INTEGER) = pf.profilefieldid
GROUP BY u.id, u.name
ORDER BY u.id;

1. 切换为EAV（实体-属性-值）模型 #

如果需要频繁按单个配置字段查询、筛选，建议拆分JSONB字段为独立表：

CREATE TABLE "UserProfile" (
    user_id INTEGER REFERENCES "users"(id),
    profilefieldid INTEGER REFERENCES "ProfileFields"(profilefieldid),
    value TEXT,
    PRIMARY KEY (user_id, profilefieldid)
);

• 优点：数据结构规范，可针对属性字段创建索引，大幅提升单属性查询性能
• 缺点：查询全量信息时需关联或透视，SQL写法更复杂，数据行数会增加

2. 保留JSONB并优化索引 #

如果主要读取用户完整信息、很少单独查询单个属性，可保留现有结构并添加GIN索引提升JSON查询性能：

CREATE INDEX idx_users_information ON "users" USING GIN(information);

• 优点：结构灵活，适配动态字段，插入更新操作便捷
• 缺点：透视查询依赖动态SQL，单属性查询性能不如EAV模型

3. 混合模式（按需选择） #

将高频查询的属性（如Company）在users表中单独设为字段，低频属性仍放在JSONB中，兼顾灵活性和查询性能。

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