Ecto fragment without parentheses

Question

My Postgres table structure:

   id   | stuff                                                                                                                                                                                                        
--------+------------------------------------------------------------
  123   | {"type1": {"ref": "ref_1", "...": "..."}, "type2": {"ref": "ref_1", "...": "..."}}

I'd like to query by ref in each type of stuff, I have a working SQL query for this:

SELECT * FROM "stuff" AS c0 CROSS JOIN jsonb_each(c0."stuff") AS f1 WHERE value->>'ref' = 'ref_1';

But using this Ecto query:

(from c in Stuff,
  join: fragment("jsonb_each(?)", c.stuff),
  where: fragment("value->>'ref' = ?", ^ref)
)
|> Repo.all

I get a Postgres syntax error in the CROSS JOIN statement:

** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near ")"

Inspecting the generated query:

[debug] QUERY ERROR source="stuff" db=0.3ms
SELECT ... FROM "stuff" AS c0 CROSS JOIN (jsonb_each(c0."stuff")) AS f1 WHERE (value->>'ref' = $1) ["ref_1"]

The above works when I remove the outer parentheses around (jsonb_each(c0."stuff")).

Is there a way to have the fragment generate the query without these parentheses or do I have to redesign the query?

Thanks

One of the obvious ways to bypass this is to use raw sql, an example can be found here https://stackoverflow.com/questions/27751216/how-to-use-raw-sql-with-ecto-repo — Sash, May 04 '18 at 15:31

score 1 · Answer 1 · answered May 04 '18 at 17:34

It seems that Ecto always wraps the join clause in parentheses, which is usually fine. The times when its not unfortunately includes certain calls like the jsonb_each above. There is a wiki here for such cases: The parentheses rules of PostgreSQL, is there a summarized guide?

The linked raw sql example had a much less upvoted answer that seems to work well with both making this query and getting back the expected struct.

sql = "SELECT * FROM "stuff" AS c0 CROSS JOIN jsonb_each(c0."stuff") AS f1 WHERE value->>'ref' = 'ref_1';"
result = JsonbTest.Repo.query!(sql)
Enum.map(result.rows, &JsonbTest.Repo.load(StuffStruct, {result.columns, &1}))

That's the approach I've taken, however, I really hope for a more Ecto way of solving this. — Sash, May 05 '18 at 09:48

score 1 · Accepted Answer · answered May 12 '18 at 23:26

1

This is a bug in ecto, has been fixed here https://github.com/elixir-ecto/ecto/issues/2537

answered May 12 '18 at 23:26

Sash

4,448
1
17
31

Ecto fragment without parentheses

2 Answers2