Arrays not being decoded as List

[fravan]

Hello there !

Gleam user here, running some SQL I stumbled upon an encoding / decoding issue with erlang lists and SQL arrays. Apologies if I use the wrong terms, I am not that familiar with erlang… 🙏

The sql is as follow:

SELECT 
  id, 
  name,
  ARRAY(
    SELECT ARRAY[user_id, role] FROM campaign_users WHERE campaign_id = id
  ) as users
FROM campaigns WHERE id = $1"

It fetches data from the campaigns table, and aggregate some other data from the campaign_users table. All in one query! There are two array being used here, otherwise postgresql is not happy (the sub query would return multiple row without the first array)

But upon receiving this, the "users" column was mapped as : [{array, ["frank", "owner"]}, {array, ["paul", "player"]}] So, a list, that contains a tuple like {array, list_of_two_items} array being an atom here.

What I was expecting was an array of array such as [ ["frank", "owner"], ["paul", "player"] ].

I don't know if it's a bug or if there's some reason for this particular use case ?

If you want to try some test on it, here is some SQL to get started:

create table users ( id text primary key );
create table campaigns ( 
  id text primary key,
  name text not null 
);
create table campaign_users (
  campaign_id text references campaigns(id),
  user_id text references users(id),
  role text
);
insert into users values ('frank'), ('paul');
insert into campaigns values ('cpg', 'the campaign');
insert into campaign_users values ('cpg', 'frank', 'owner'), ('cpg', 'paul', 'player');