Search code examples
postgresqlectojsonb

Ecto fragment without parentheses

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

Solution

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