如何将多列组合成单个数组或类似数组? [英] How to group multiple columns into a single array or similar?
本文介绍了如何将多列组合成单个数组或类似数组?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望我的查询返回如下结构的结果,其中tags
是数组或类似的数组:
id | name | tags
1 a [[1, "name1", "color1"], [2, "name2", color2"]]
2 b [[1, "name1", "color1"), (3, "name3", color3"]]
我希望此查询可以工作,但它给了我一个错误:
SELECT i.id, i.name, array_agg(t.tag_ids, t.tag_names, t.tag_colors) as tags
FROM ITEMS
LEFT OUTER JOIN (
SELECT trm.target_record_id
, array_agg(tag_id) as tag_ids
, array_agg(t.tag_name) as tag_names
, array_agg(t.tag_color) as tag_colors
FROM tags_record_maps trm
INNER JOIN tags t on t.id = trm.tag_id
GROUP BY trm.target_record_id
) t on t.target_record_id = i.id;
错误:
PG::UndefinedFunction: ERROR: function array_agg(integer[], character varying[], character varying[]) does not exist LINE 1: ..., action_c2, action_c3, action_name, action_desc, array_agg(... HINT: No function matches the given name and argument types. You might need to add explicit type casts.
此查询工作并产生类似的结果(但不完全是我想要的结果):
SELECT i.id, i.name, t.tag_ids, t.tag_names, t.tag_colors as tags as tags
FROM ITEMS
LEFT OUTER JOIN (
SELECT trm.target_record_id, array_agg(tag_id) as tag_ids, array_agg(t.tag_name) as tag_names, array_agg(t.tag_color) as tag_colors
FROM tags_record_maps trm
INNER JOIN tags t on t.id = trm.tag_id
GROUP BY trm.target_record_id
) t on t.target_record_id = i.id;
结果:
id | name | tag_ids | tag_names | tag_colors
1 a [1, 2] ["name1, "name2"] ["color1", "color2"]
1 a [1, 3] ["name1, "name3"] ["color1", "color3"]
编辑:
这个查询除了将json键命名为f1
、f2
、f3
之外,几乎产生了我想要的结果。如果我能将它们命名为id
、name
、color
:,那就太好了
SELECT trm.target_record_id, json_agg( (t.id, t.tag_name, t.tag_color) )
FROM tags_record_maps trm
INNER JOIN tags t on t.site_id = trm.site_id and t.id = trm.tag_id
GROUP BY trm.target_record_id
having count(*) > 1;
结果:
[{"f1":1,"f2":"name1","f3":"color1"},{"f1":2,"f2":"name2","f3":"color2"}]
推荐答案
(t.id, t.tag_name, t.tag_color)
是ROW(t.id, t.tag_name, t.tag_color)
的简短语法,ROW constructor不保留嵌套的属性名称。The manual:
默认情况下,
ROW
表达式创建的值属于匿名记录类型。如有必要,可以将其强制转换为命名复合类型- 表的行类型,或使用CREATE TYPE AS
。
强调我的。为了在结果中也获得正确的键名称,按照引号中的建议将强制转换为已注册的复合类型,使用嵌套子选择,或者在Postgres 9.4或更高版本中使用json_build_object()
(有效地避免了事先使用行构造函数):
SELECT trm.target_record_id
, json_agg(json_build_object('id', t.id
, 'tag_name', t.tag_name
, 'tag_color', t.tag_color)) AS tags
FROM tags_record_maps trm
JOIN tags t USING (site_id)
WHERE t.id = trm.tag_id
GROUP BY trm.target_record_id
HAVING count(*) > 1;
我使用原始列名,但您可以自由选择键名称。在您的案例中:
json_agg(json_build_object('id', t.id
, 'name', t.tag_name
, 'color', t.tag_color)) AS tags
详细说明:
这篇关于如何将多列组合成单个数组或类似数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文