sql - PostgreSQL左连接查询具有一对多关系
问题描述
我有下表
CREATE TABLE employee(id serial PRIMARY KEY,employee jsonb);
CREATE TABLE perks_details(id serial PRIMARY KEY,details jsonb);
insert into employee(employee) values
('{"name": "name1",
"perks": [
{"id": 123, "valid_from": "T23:28:56.782Z"},
{"id": 456, "valid_from": "T23:28:56.782Z"},
{"id": 789, "valid_from": "T23:28:56.782Z"}
]
}');
insert into perks_details(details) values('{"id":123,"detail1":"lorem","detail2":"lorem"}');
insert into perks_details(details) values('{"id":123,"detail3":"lorem","detail4":"lorem"}');
insert into perks_details(details) values('{"id":456,"detail5":"lorem","detail6":"lorem"}');
如何在 perks_details 表和聚合函数中使用 id 的 perks id 的employee
左连接编写选择查询,以便我的结果数据如下所示:perks_details
{
"name": "name1",
"perks": [
{
"id": 123,
"valid_from": "T23:28:56.782Z",
"details": [
{
"id": 123,
"detail1": "lorem",
"detail2": "lorem"
},
{
"id": 123,
"detail3": "lorem",
"detail4": "lorem"
}
]
},
{
"id": 456,
"valid_from": "T23:28:56.782Z",
"details": [
{
"id": 456,
"detail5": "lorem",
"detail6": "lorem"
}
]
},
{
"id": 789,
"valid_from": "T23:28:56.782Z",
"details": []
}
]
}
我尝试了多种变体,但无法接近有效的查询。请在这里给我建议/帮助。
更新我有这个带有左连接的查询,但结果不是我所期望的
select e.id, perk, perks_details.details
from employee AS e, jsonb_array_elements(employee->'perks') perk
LEFT JOIN perks_details on perks_details.details -> 'id' = perk->'id'
group by e.id, perk.value, perks_details.details;
解决方案
您好,您可以这样做:
with cte as (
select t1.employee->>'name' "name",
t2.id, t2.valid_from, json_agg(t3.details) "details"
from employee t1
cross join lateral jsonb_to_recordset(employee->'perks') as t2(id int,valid_from time)
left join perks_details t3 on t2.id::text=t3.details->>'id'
group by 1,2,3
)
select row_to_json(t) from (
select
name,
json_agg(jsonb_build_object('id',id,'valid_from',valid_from,'details',details)) "perks"
from cte group by name
) t
推荐阅读
- c# - 如何更改 LockScreen 背景(查找 windows.system.UserProfile.dll)?
- kubernetes - 一个完整的 Kubernetes pod 是否仍然保留所需的资源?
- c++ - 使用 MinGW 构建 ASSIMP 导致文件太大错误
- javascript - 为什么悬停时图像不会变为以前的图像?
- postgresql - mat视图的依赖关系示意图
- c# - 将已发布文件的内容从 Angular 读取到 C#
- python - 使用 Python 进行 MSAL 身份验证显示未经授权的客户端?
- c - 在C中的循环内分配和释放内存
- java - 通过 Bson 过滤器时,Mongo 函数 updatemany() 不起作用
- python-3.x - Pycharm 在进行自动完成/类型检查时使用不完整的存根