PostgreSQL以数组形式从多对多关系返回数据

Question

我正在寻找存储和检索数据数组的最佳方式。我目前正在实现的解决方案使用了如下的多对多关系。

venue_themes

user_id  style  environment
A1A2     formal  indoor
A2B2     formal  outdoor

theme_setting_to_setting_enum

id  user_id  setting_enum_id
1   A1A2       1
2   A1A2       3 
3   A2B2       1 
4   A2B2       2

设置_编号

id  value
1   garden
2   beach
3   golf course
4   backyard

我目前的查询是：

SELECT vt.user_id, vt.style, vt.environment, se.value FROM venue_themes vt JOIN theme_settings_to_setting_enum ts ON vt.user_id = ts.user_id JOIN setting_enum se ON ts.setting_enum_id = se.id GROUP BY vt.user_id, ts.id, se.id;

这是有效的，但它返回了多个具有相同数据的行，而不是我的设置枚举值。

返回的示例是：

user_id    style    environment   value
AAAA       formal   indoor        beach
AAAA       formal   indoor        backyard
AAAA       formal   indoor        tent

这很好，但如果我有很多价值观的话，这似乎太过分了。我真正想要的数据是：

user_id    style    environment   value
AAAA       formal   indoor        beach, backyard, tent

理想情况下，我会在数组或类似的东西中返回我的值，这样我就不必构建一个函数来处理返回的数据。

Answer 1

您可以从GROUP BY子句中删除se.id，并使用STRING_AGG()生成CSV字符串：

SELECT vt.user_id, vt.style, vt.environment, STRING_AGG(se.value, ', ') se_values
FROM venue_themes vt 
JOIN theme_settings_to_setting_enum ts ON vt.user_id = ts.user_id 
JOIN setting_enum se ON ts.setting_enum_id = se.id 
GROUP BY vt.user_id;

假设user_id是venue_themes的主键，那么在GROUP BY子句中只有这一列就足够了(表的其他列在操作上依赖于主键(。

您可以使用ORDER BY子句控制值在字符串中聚合的顺序：

STRING_AGG(se.value, ', ' ORDER BY se.id) se_values

如果您想要一个数组而不是CSV字符串，那么使用ARRAY_AGG():

ARRAY_AGG(se.value, ', ' ORDER BY se.id) se_values