sql - 如何在 SQL 中获取对象(具有所有键名)中的对象?
问题描述
我的数据库包含两个名为 category 和 sport 的表:
category
-----------------------------------
id | title | ...
1 | summer
2 | autumn
3 | spring
4 | winter
sport
--------------------------------------------------------------
id | title | description | category_id | ...
1 | ski | ... | 4
2 | surf | ... | 1
3 | snorkeling | ... | 1
4 | running | ... | 3
5 | hiking | ... | 2
...
我的问题是如何通过 Sql (postgresql) 中的一个请求获得这个结果:
{
category: [
{
c.id: 1,
c.title: summer,
sport: [
{
s.id: 2,
s.title: surf,
},
{
s.id: 3,
s.title: snorkeling,
},
],
},
{
c.id: 2,
c.title: autumn,
sport: [
{
s.id: 5,
s.title: hiking
}
],
},
{
c.id: 3
c.title: spring,
sport: [
{
s.id: 4,
s.title: running,
}
],
},
{
...
}
]
}
我尝试过使用 ARRAY_AGG,但它删除了 key_names,我需要它在我的 API 中调用这些值。
解决方案
你需要结合jsonb_agg()
,to_jsonb() and
jsonb_build_object()`
select to_jsonb(c)||jsonb_build_object('sport', jsonb_agg(to_jsonb(s) - 'category_id'))
from category c
join sport s on s.category_id = c.id
group by c.id;
以上假设category.id
被定义为主键。
每个类别返回一行。
如果你真的想要一个包含所有行的巨大数组,你需要分两步聚合:
select jsonb_build_object('category', jsonb_agg(to_jsonb(c)||sport))
from category c
join (
select category_id, jsonb_build_object('sport', jsonb_agg(to_jsonb(s) - 'category_id')) as sport
from sport s
group by s.category_id
) s on s.category_id = c.id
推荐阅读
- javascript - 如何让滚动事件通过覆盖而不是单击事件
- sql - 为别名语句添加过滤器
- redmine - 有没有办法确定将“estimated_hours”添加到Redmine中的问题的日期?
- php - 如何从 WooCommerce 订单确认电子邮件中删除付款方式?
- php - 使用索引而不是名称提取数据
- android - Android中的SQLite数据库不返回任何内容
- python-attrs - 在 attrs 自动生成的 init 方法签名中禁用前导下划线删除
- javascript - 如何在Angular 8中的逗号后添加空格
- python - 与 Dask 一起使用时增加 Tornado 读取缓冲区大小
- python - 当你的包被其他人导入时如何让pyInstaller找到数据文件