如何将PostgreSQL中JSONB列的JSON对象展开为多列？

一、静态指定属性列（已知需要的属性） #

如果已经明确要提取哪些属性，直接用->>（提取字符串值）和->（提取JSON/数组值）操作符即可，保留原数据类型：

SELECT 
  username,
  attributes->>'cn' AS cn,
  attributes->>'sn' AS sn,
  attributes->'uid' AS uid,
  attributes->'mail' AS mail,
  attributes->>'displayName' AS displayName
FROM idp_accounts 
WHERE username = 'visser';

• ->>：将JSONB中的字符串属性转为文本类型，适合cn、sn这类单值字符串。
• ->：保留JSONB类型，适合uid、mail这类数组结构，查询结果会保持原数组格式。
• 若某用户没有对应属性，该列会返回NULL，不影响整体查询。

二、动态生成所有属性列（属性不固定时） #

如果用户属性差异大，需要自动把所有存在的属性转为列，可以用动态SQL实现：

步骤1：获取所有唯一属性键 #

先查询表中所有出现过的属性名：

SELECT DISTINCT jsonb_object_keys(attributes) AS key
FROM idp_accounts;

步骤2：生成动态查询语句 #

用string_agg拼接所有属性的提取逻辑，生成完整可执行的SQL：

WITH all_keys AS (
  SELECT DISTINCT jsonb_object_keys(attributes) AS key
  FROM idp_accounts
)
SELECT format(
  'SELECT username, %s FROM idp_accounts',
  string_agg(format('attributes->%L AS %I', key, key), ', ')
)
FROM all_keys;

执行这条SQL会得到一个自动生成的查询语句，里面包含了所有属性列。复制这个语句执行，就能得到所有用户的属性按列展示的结果。

比如针对你的示例数据，生成的SQL会是：

SELECT username, attributes->'dn' AS dn, attributes->'sHO' AS "sHO", attributes->'ePSA' AS "ePSA", attributes->'email' AS email, attributes->'cn' AS cn, attributes->'sn' AS sn, attributes->'uid' AS uid, attributes->'mail' AS mail, attributes->'displayName' AS "displayName" FROM idp_accounts;

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