PostgreSQL 15能否将可变键JSONB转换为指定结构表？

问题 #

给定如下JSONB结构（顶级键数量不固定，每个键对应包含e1/e2/e3的子对象）：

{
  "a": {
    "e1": 10,
    "e2": 30,
    "e3": 50
  },
  "b": {
    "e1": 20,
    "e2": 40,
    "e3": 60
  }
  // 支持更多顶级键
}

需要通过PostgreSQL 15的JSON函数与运算符，生成如下关系表：

e1 | e2 | e3 
---+----+----
10 | 30 | 50 
20 | 40 | 60 
// 对应更多行

解决方案 #

可以通过jsonb_each()拆分顶级键值对，再提取子对象字段实现：

场景1：JSONB存储在表中 #

假设数据存储在test_table的data字段，执行以下SQL：

SELECT
  (value ->> 'e1')::INT AS e1,
  (value ->> 'e2')::INT AS e2,
  (value ->> 'e3')::INT AS e3
FROM test_table,
     jsonb_each(data);

场景2：直接使用JSON字面量 #

如果是直接处理JSON字面量，用以下语句：

SELECT
  (value ->> 'e1')::INT AS e1,
  (value ->> 'e2')::INT AS e2,
  (value ->> 'e3')::INT AS e3
FROM jsonb_each('{
  "a": {"e1":10,"e2":30,"e3":50},
  "b": {"e1":20,"e2":40,"e3":60}
}'::JSONB);

语句说明 #

• jsonb_each(data)：将JSONB对象的每个顶级键值对拆分为独立行，每行包含key（原顶级键名）和value（对应子JSON对象）字段。
• value ->> 'e1'：提取子对象中e1的字符串值，::INT将其转为整数类型（字段类型不同可调整转换方式）。
• 最终将三个提取字段作为列，得到目标表结构。

内容的提问来源于stack exchange，提问作者luis-fdz