Ecto: JSONB fragment

[maxsalven]

Environment

• Elixir version (elixir -v): 1.6.1
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): Postgres 9.6.1
• Ecto version (mix deps): 2.1.6
• Database adapter and version (mix deps): 0.13.4
• Operating system: MacOS 10.12.4

Current behavior

workout.details is a JSONB field with a shape like {"movements": [{"id": 2}]}

Interpolating by a fragment in a JSONB query fails:

 movement_id = 2
 from wo in Workout,
        where: fragment(~s(?#>'{movements}'@>'[{"id": ?}]'), wo.details, ^movement_id)

Error: invalid input syntax for type json

A direct SQL query works:

SELECT *
FROM workouts
WHERE workouts.details#>'{movements}'@>'[{"id":2}]'

I have also tried

json = ~s('[{"id": #{movement_id}}]')
from wo in Workout, where: fragment(~s(?#>'{movements}'@>?), wo.details, ^json)

Which runs successfully, but incorrectly returns no results.

Hardcoding the id works as expected:

 from wo in Workout,
        where: fragment(~s(?#>'{movements}'@>'[{"id": 2}]'), wo.details)

[michalmuskala]

You can't put value placeholders inside of strings in fragments. All the database will see is a string with ? (or $n) inside of it - it won't know it has to interpolate into that string.

A solution is to build the whole json value on the elixir side:

 movement_id = 2
 from wo in Workout,
   where: fragment(~s(?#>'{movements}' @> ?), wo.details, ^[%{id: movement_id}])