PostgreSQL Ecto - DISTINCT inside array_agg(json_build_object

Question

I'm trying to use array_agg to incorporate an array into a single record.

This is the mysql I am now trying to achieve using Ecto PostgreSQL:

SELECT p.p_id, p.`p_name`, p.brand, GROUP_CONCAT(DISTINCT CONCAT(c.c_id,':',c.c_name) SEPARATOR ', ') as categories, GROUP_CONCAT(DISTINCT CONCAT(s.s_id,':',s.s_name) SEPARATOR ', ') as shops

In PostgreSQL, GROUP_CONCAT becomes array_agg

This is working but I need to make it only show DISTINCT categories and shops. I needed to add json_build_object to have more than just the id field of category and shop shown.:

def create_query(nil, categories, shop_ids) do
  products_shops_categories = from p in Product,
  join: ps in ProductShop, on: p.id == ps.p_id,
  join: s in Shop, on: s.id == ps.s_id,
  join: pc in ProductCategory, on: p.id == pc.p_id,
  join: c in Subcategory, on: c.id == pc.c_id,
  where: c.id in ^categories,
  where: s.id in ^shop_ids,
  group_by: [p.id, s.id],
  select: %{product: p, categories: fragment("array_agg(json_build_object('id', ?, 'name', ?))", c.id, c.name), shops: fragment("array_agg( json_build_object('id', ?, 'name', ?, 'point', ?))", s.id, s.name, s.point)}
end

When I add DISTINCT into the array_agg it causes:

** (Postgrex.Error) ERROR 42809 (wrong_object_type): DISTINCT specified, but json_build_object is not an ag
gregate function

Final solution thanks to Pozs's suggestion:

def create_query(nil, categories, shop_ids) do
    products_shops_categories = from p in Product,
    distinct: p.id,
    join: ps in ProductShop, on: p.id == ps.p_id,
    join: s in Shop, on: s.id == ps.s_id,
    join: pc in ProductCategory, on: p.id == pc.p_id,
    join: c in Subcategory, on: c.id == pc.c_id,
    where: c.id in ^categories,
    where: s.id in ^shop_ids,
    group_by: [p.id, p.name, p.brand],
    select: %{product: p, categories: fragment("json_agg( DISTINCT (?, ?)) AS category", c.id, c.name), shops: fragment("json_agg( DISTINCT (?, ?, ?)) AS shop", s.id, s.name, s.point)}
  end

Regarding the solution by a_horse_with_a_name below:

I tried it just with categories:

group_by: [p.id, p.name, p.brand],
select: %{product: p, categories: fragment("string_agg(DISTINCT CONCAT(?,':',?), ', ') AS categories", c.id, c.name), shops: fragment("json_agg( DISTINCT (?, ?, ?)) AS shop", s.id, s.name, s.point)}

And it also works. Just depends what format you want the array to be in. sting_agg is a comma delimited string.

Answer 1

Use string_agg() instead of group_concat():

SELECT p.p_id, 
       p.p_name, 
       p.brand, 
       string_agg(DISTINCT CONCAT(c.c_id,':',c.c_name), ', ') as categories, 
       string_agg(DISTINCT CONCAT(s.s_id,':',s.s_name), ', ') as shops
FROM ..