PostgreSQL中如何将JSON数组字段反序列化为结构化多行表

核心逻辑 #

你的data字段存储的是JSON数组，数组内每个元素是独立的JSON对象，只需要两步就能完成转换：

1. 拆分数组：用内置函数把外层[]包裹的数组拆成多行，每行对应一个{}包裹的JSON对象
2. 提取字段：从拆分后的单个JSON对象中，取出type、user对应的属性值

具体实现代码 #

如果你的data字段是json类型，直接执行以下语句即可：

SELECT
    elem->>'type' AS type,
    elem->>'user' AS "user"
FROM panel,
     json_array_elements(data) AS elem;

如果data字段是jsonb类型，把拆分函数替换为jsonb_array_elements即可，其余写法完全一致。

补充说明 #

• 上述写法用了PostgreSQL的隐式LATERAL关联，会自动为panel表的每一行执行数组拆分操作：长度为N的数组会被展开为N行，自动和原表行关联，不需要额外写关联条件。
• ->>是JSON取值操作符，返回的是文本格式的属性值；如果需要把user字段转为数值类型，可以加类型强转：(elem->>'user')::int AS user
• 如果表中存在data为NULL、不是合法JSON数组的脏数据，可以加过滤条件避免报错：

  WHERE data IS NOT NULL AND json_typeof(data) = 'array'
  

执行后返回的结果和你预期的结构化表完全一致。

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