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');

[tsloughter]

Thanks for the issue.

I am not sure why it is that way. It was clearly on purpose:

type_spec() ->
    "[Element::term() | null | {array, SubArray}]".

Maybe it is to remove some ambiguity that could happen otherwise... Or maybe it should even be:

{array, [{array, ["frank", "owner"]}, {array, ["paul", "player"]}]}

I'll have to dig around some more.

[tsloughter]

I suppose one possibility is if you return json it could be a list. But that actually speaks to another issue that since the first level isn't wrapped in array it is indistinguishable from a json column itself without knowing the type of the column returned. Hmm.. I almost want to wrap both of those in array and json but I don't want to break backwards compatibility even if this isn't 1.0 yet.

[fravan]

Is this issue the one you are talking about: #78 ? 🤔

Maybe we should ask more users or libraries that uses pgo what they thing about this ? I talked a bit about this with lpil before opening the issue. Maybe he might have more knowledge on what to decide/change ?

I don't know much about all of this to be honest 😅

[tsloughter]

That json one is different. I think it just needs to call json to decode instead of leaving it as a string. Right now you have to configure a json module to use to decode json or it gets left as a string like in that example.

The issue is for an array of an array of strings in postgres the OID you get back in the protocol only says it is a text array, even though it is a text array array. I don't get why or if I'm missing something somewhere. So to know it is a subarray and not a charlist or a json element we have {array, ..}.

I'm open to dropping charlist support or strings but that still eaves json. Which might mean wrapping json like, {json, ...}.

so yea, issue is we don't know how deep the arrays go, only that it is an array and the type once you go all the levels down is type T, and a list could mean 3 different types (charlist, array or json list).