Ecto does not return a list for an aggregate function (via fragment)

Questions / Help
,
1

Hello :wave:

Given the following commerce-like DB context:

Item: { id: integer, model: string ...}

Shop: { id: integer, name: string ...}

# many-to-many table to store what Item's each Shop sells

ShopItem: { shop_id: integer, item_id: integer}

My use-case now tries to aggregate, for each Item, all the Shop.id's where that Item is sold at.

This is my Ecto query:

query = from i in Item, 
              join: si in ShopItem, 
              on: si.item_id == i.id, 
              group_by: i.id, 
              select: {i.id, fragment("ARRAY_AGG(?)", si.shop_id)}

# Which when run
iex()> query |> Repo.all

# Returns something like ( limited to 20 results)
[
  {1, [16, 41, 21, 39]},
  {2, [27, 15, 50, 35]},
  {3, [1, 53, 27, 15]},
  {4, [43, 39, 29, 2]},
  {5, [40, 32, 21, 2]},
  {6, [5, 25, 52, 20]},
  {7, [14, 8, 40, 20]},
  {8, '%1*\b'},
  {9, [3, 41, 37, 31]},
  {10, [48, 12, 17, 50]},
  {11, [32, 4, 7, 27]},
  {12, [6, 1, 5, 7]},
  {13, [31, 34, 10, 1]},
  {14, [42, 32, 28, 52]},
  {15, [25, 3, 29, 50]},
  {16, [13, 14, 1, 51]},
  {17, ' +\b%'},
  {18, [51, 53, 29, 41]},
  {19, [14, 52, 7, 1]},
  {20, [30, 19, 33, 2]}
]

As you can see, Items 8 and 17 result in a weird aggregation; not a List but a charlist.

If I instead replace ARRAY_AGG with JSON_AGG to see what’s up in there, the maps appear in the aggregation just fine:

query = from i in Item, 
              join: si in ShopItem, 
              on: si.item_id == i.id, 
              group_by: i.id, 
              select: {i.id, fragment("JSON_AGG(?)", si)}   # <--  Change function here

iex()> query |> Repo.all

#result
[
  {1,
   [
     %{
       "id" => 1,
       "shop_id" => 16,
     },
     %{
       "id" => 2,
       "shop_id" => 41,
     },
     %{
       "id" => 3,
       "shop_id" => 21,
     },
     %{
       "id" => 4,
       "shop_id" => 39,
     }
   ]},
   #... more items
  {8,   # <-- conflictive Item 8 before, seems to have relationships perfectly fine here
   [
     %{
       "id" => 37,
       "shop_id" => 37,  
     },
     %{
       "id" => 38,
       "shop_id" => 49,
     },
     %{
       "id" => 39,
       "shop_id" => 42,
     },
     %{
       "id" => 40,
       "shop_id" => 8,
     }
   ]},
   #... more items
]

I run the equivalent query in raw (postgre)SQL and the relationships are also there, perfectly OK. For some reason there are some Item where the fragment-ed ARRAY_AGG is failing to return a List.

# Raw SQL
select 
	i.id,
	array_agg(si.shop_id) as sellers
	from (select * from items limit 200) as i
inner join shops_items as si
on si.item_id = i.id
group by i.id

# Returns
1	{16,41,21,39}
2	{27,15,50,35}
3	{1,53,27,15}
4	{43,39,29,2}
5	{40,32,21,2}
6	{5,25,52,20}
7	{14,8,40,20}
8	{37,49,42,8}   # <--- same relationships as the JSON_AGG case, so it's consistent
9	{3,41,37,31}
10	{48,12,17,50}
11	{32,4,7,27}
12	{6,1,5,7}
13	{31,34,10,1}
14	{42,32,28,52}
15	{25,3,29,50}
16	{13,14,1,51}
17	{32,43,8,37}
18	{51,53,29,41}
22	{40,28,10,37}

Any ideas what’s causing my problem? Thank you!

2

That’s because in the first one ARRAY_AGG returns a list of integer, which are considered charlist in Elixir: Binaries, strings, and charlists - The Elixir programming language

The second version returns tuples so that’s not an issue.

3 Likes
3

Thanks @edisonywh for the reply. You’re right, I didn’t even bother to check it they coincided with their integer representation :man_facepalming: The aggregation works well, it’s only the terminal interpreter which is confusing sometimes if you forget the charlist as integer vs ascii :smiley: