sql - 在postgres中查询以表格形式查看jsonb数据列的动态值

Question

在“快照”表中，我按以下顺序排列

id(uuid) || snapShotId(uuid) || resultData(jsonb) || createdAt(timestamp)

“resultData”列中的数据是 jsonb 类型，它存储的动态数据如下：

[{id: 1, name: 'Ram', age: 23, }]
[{id:2, title: 'Some title', release_year: '1995'}, {id:3, title: 'Some title 1', release_year: '1996'}]
[{id:4, email: '[e-mail]'}]

我想要的是编写一个查询来查看具有表格格式动态值的“resultData”列的列：

结果将如下所示：

**id || name || age || title || email || release_year**

1  || Ram  || 23  ||   null       ||   null   || null
2  || null || null || some title  || null || 1995
3  || null || null || some title 1 || null || 1996
4  || null  || null || null  || [e-mail] || null

标签： sqljsonpostgresql

Answer 1

您可以jsonb_array_elements为此使用：

select r.item ->> 'id' as id,
       r.item ->> 'name' as name,
       r.item ->> 'age' as age,
       r.item ->> 'title' as title, 
       r.item ->> 'email' as email,
       r.item ->> 'release_year' as release_year
from snapshot
  cross join jsonb_array_elements(result_data) as r(item)

在线示例