Люди добрые, а как можно получить только один вложенный массив? Например,

{1-electrical-components} или {1-electrical-components,2-motor-control-and-protection,3-magnetic-contactors}, но не {{}}.


SELECT p.product_id,
ARRAY_AGG(cv.slugs) FILTER (WHERE cv.slugs IS NOT NULL) AS category_slugs
FROM products AS p
LEFT JOIN shop_settings AS ss
ON ss.product_id = p.product_id
AND ss.deleted = FALSE
LEFT JOIN brands AS b
ON b.brand_id = p.brand_id
LEFT JOIN categories_view AS cv
ON cv.category_id = p.category_id
GROUP BY p.product_id;


product_id | category_slugs
--------------------------------------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------
cc415d6c-02b4-4f1c-b0eb-f50bd436d516 | {{1-electrical-components,2-motor-control-and-protection}}
58bd6c91-8ff5-4b4e-a822-b5bed849986b | {{1-electrical-components}}
0f69a750-c902-430a-ac11-11f9964d353e | {{1-electrical-components,2-motor-control-and-protection,3-magnetic-contactors},{1-electrical-components,2-motor-control-and-protection,3-magnetic-contactors}}
nnn |
1 |

а что у вас в cv.slugs лежит, уже массив?

Да, {1-electrical-components,2-motor-control-and-protection,3-magnetic-contactors}

Агрегат самодельный тут вписывается неплохо: ``` CREATE AGGREGATE array_accum (anyarray) ( sfunc = array_cat, stype = anyarray, initcond = '{}' ); SELECT ARRAY_ACCUM(DISTINCT cv.slugs) product_id | array_accum --------------------------------------+-------------------------------------------------------------------------------- 0f69a750-c902-430a-ac11-11f9964d353e | {1-electrical-components,2-motor-control-and-protection,3-magnetic-contactors} 1 | {} 58bd6c91-8ff5-4b4e-a822-b5bed849986b | {1-electrical-components} cc415d6c-02b4-4f1c-b0eb-f50bd436d516 | {1-electrical-components,2-motor-control-and-protection} nnn | {} ``` https://www.postgresql.org/docs/9.2/xaggr.html https://stackoverflow.com/questions/43472482/postgres-array-agg-throws-cannot-accumulate-empty-arrays-for-empty-arrays