Ecto coalesce query translating to empty object, is this a bug?

Questions / Help
, ,
1

Hi! While working with JSON data the other day, I noticed that coalesce(q.field, []) is being translated to coalesce(f0."o", '{}') instead of coalesce(f0."o", '[]'), which is kinda annoying because the former will result in an empty map and not an empty list like the later.

I thought this was related to JSON specifically, but I managed to create a small repro and it seems there’s something here that I’m not aware of:

 Repo.all(from x in fragment("select null AS o"), select: coalesce(x.o, []))

Do you guys have any idea why this is translated to the query bellow?

SELECT coalesce(f0."o", '{}') FROM (select null AS o) AS f0 []

In the meantime, I’m just specifying the proper coalesce clause as a fragment: fragment("COALESCE(?, '[]')", q.field), but it feels this should be supported!?

2

Well, {} is a literal empty array in Postgres. That’s why it isn’t translated properly.

3

Yeah, is it {} or '{}'? The former is an empty pg array, the latter is a string of {}. If you’re looking for json you want type([], :json).

4

Sure, could you elaborate? COALESCE(?, '[]') works, but Ecto is decoding '{}' and '[]' differently. So I’m not sure what would be the correct syntax in this case.

I think the single-quoted version is the correct syntax for COALESCE here. Unfortunately json is not an Ecto type, but what you can do is: type(coalesce(x.o, []), {:array, :map}), but this doesn’t really work if what you want is to coalesce a jsonb column because it will try to cast it to jsonb[], for instance:

Repo.all(
  from x in fragment("select json_build_object(null) AS o"),
  select: type(coalesce(x.o, []), {:array, :map})

I think this has to do with Ecto’s preference to handle jsonb as a map.

5

Postgres has a native array type. If you want json you need to typecast to json where ecto cannot infer that it‘s dealing with json.

1 Like
7

Ok, I think I understand what I’m getting wrong here…

The thing is: coalesce(q.field, []) is indeed getting properly translated into the “correct” underlying Postgres representation, so it uses {}. The problem though is that when translating it back from jsonb, the texts for {} and [] will have different semantics; so '{}' correctly becomes %{} and '[]' correctly becomes a [] :man_facepalming:.

Update: In case someone else sees this, the following statement returns a text type and not an actual empty array, which is why this behavior is confusing when dealing with different data types: select pg_typeof(coalesce(null, '{}')).