Trouble with group_by of a dynamic jsonb field

Questions / Help
1

I’m having some trouble querying a jsonb record and wonder if anyone here has some insight.

The following query errors out with:
ERROR 42803 (grouping_error): column "t0.answers" must appear in the GROUP BY clause or be used in an aggregate function

field_name = "field_one"
Repo.all(
      from(
        t in Ticket,
        where: ^dynamic,
        where: fragment("answers \\? ?", ^field_name),
        select: %{name: fragment("answers->>?", ^field_name), value: count(t.id)},
        group_by: fragment("answers->>?", ^field_name)
      )
)

But this one runs fine:

field_name = "field_one"
Repo.all(
      from(
        t in Ticket,
        where: ^dynamic,
        where: fragment("answers \\? ?", ^field_name),
        select: %{name: fragment("answers->>?", "field_one"), value: count(t.id)},
        group_by: fragment("answers->>?", "field_one")
      )
)

It appears at this point that postgres cannot make use of parameterised variables in a group_by clause looking inside a jsonb record.

I’m thinking to just generate the sql by hand for the two fragments, but Ecto really really doesn’t want to me to pass anything other than a literal string as the first parameter of fragment.

This query also contains a potentially large and complex dynamic part that I need ecto to compile.

Suggestions welcome please!

2

This is what I came up with. Not pretty. Improvement suggestions welcome

query =
      from(
        t in Ticket,
        select: t.id,
        where: ^dynamic
      )

    safe_field_name = Field.normalize(name)

    {"SELECT t0.\"id\" FROM \"tickets\" AS t0 WHERE " <> dynamic_sql, vars} =
      Repo.to_sql(:all, query)

    sql = """
    SELECT answers->>'#{safe_field_name}', count(t0."id") FROM "#{Triplex.to_prefix(domain)}"."tickets" AS t0 WHERE #{
      dynamic_sql
    } AND (answers ? '#{safe_field_name}') GROUP BY answers->>'#{safe_field_name}'
    """

    case Repo.query(sql, vars) do
      {:ok, %Postgrex.Result{rows: rows}} ->
        Enum.map(rows, fn [name, count] -> %{name: name, value: count} end)

      _ ->
        []
    end