Ecto: Token "$" is invalid.

[rafaeljesus]

Environment

• Elixir version 1.3.2:
• Database and version (PostgreSQL 9.5.3):
• Ecto version (2.0.5):
• Phoenix Ecto version (3.0.1):
• Database adapter and version (postgrex, 0.12.1):
• Operating system: Mac OS
  

  Current behavior

I have a model (candidate) with a jsonb field (experiences)

candidate_migration.ex

add :experiences, :jsonb

candidate.ex

field :experiences, {:array, :map}, default: []

def filter_candidates_for_job(job) do
# yes, job position is a number and does not comes with `$` character
  from(c in Candidate, where:
    fragment("?@>'[{\"position\": ?}]'", c.experiences, ^job.position))
  |> Repo.all
end

When I run the test of filter_candidates_for_job method

My test fails with the following stack trace

10:49:52.285 [info]  QUERY ERROR source="candidates" db=1.1ms
SELECT c0."id", c0."phone", c0."email", c0."name", c0."cpf", c0."origin", c0."status", c0."salary", c0."confirmation_type", c0."education", c0."lead", c0."gender", c0."negotiable_salary", c0."exclude_from_matching", c0."birthdate", c0."confirmation_date", c0."user", c0."extra", c0."address", c0."drivers_license", c0."experiences", c0."inserted_at", c0."updated_at" FROM "candidates" AS c0 WHERE (c0."experiences"@>'[{"position": $1}]') [5]

  1) test when filter candidates job filters candidates for an job (MatchMaker.CandidateTest)
     test/models/candidate_test.exs:34
     ** (Postgrex.Error) ERROR (invalid_text_representation): invalid input syntax for type json

   Token "$" is invalid.
     stacktrace:
       (ecto) lib/ecto/adapters/sql.ex:429: Ecto.Adapters.SQL.execute_and_cache/7
       (ecto) lib/ecto/repo/queryable.ex:127: Ecto.Repo.Queryable.execute/5
       (ecto) lib/ecto/repo/queryable.ex:40: Ecto.Repo.Queryable.all/4
       test/models/candidate_test.exs:35: (test)

Then I see the query is malformed with a invalid $:

• WHERE (c0."experiences"@>'[{"position": $1}]')

Expected behavior

Fragment function should support ? for postgres jsonb queries

An fragment("?@>'[{\"position\": ?}]'", c.experiences, ^job.position))

Should create the sql query WHERE (c0."experiences"@>'[{"position": 3}]')

[josevalim]

@rafaeljesus the problem is that Elixir will escape any value interpolated in a fragment to for security reasons. What you need to do is to interpolate the whole value:

json = "[{\"position\": #{job.position}}]"
fragment("?@>?", c.experiences, ^json))

The above or similar should work although you should probably use something like Poison to generate the json.

[rafaeljesus]

Thx for your quick reply @josevalim

I tried your suggestion the error is gone but the query does not returns anything:

json = Poison.encode!([%{position: job.position}])

or
json = "[{\"position\": #{job.position}}]"

or
json = "[{\"position\": some_num_i_know_is_on_db}]"

fragment("?@>?", c.experiences, ^json)

Is generating the following output

AS c0 WHERE (c0."experiences"@>$1) ["[{\"position\": 3}]"]

But is not returning any records 😕

I know this works for example:

WHERE (c0."experiences"@>'[{"position": $1}]') [3]

Any thoughts?

[josevalim]

@rafaeljesus can you run a query directly against the database and paste a working query here?

[rafaeljesus]

@josevalim running a directly on database:

INSERT INTO candidates (experiences) VALUES ('[{"position": 1}]')

SELECT * FROM candidates WHERE experiences @> '[{"position": 1}]'
# It does returns data

Given ecto logs:

as c WHERE (c."experiences" @> $1) ["[{\"position\": 1}]"]

If ecto would be sending the following query to db

WHERE experiences @> "[{\"position\": 1}]"

We would have a errorSyntax error at or near "position"

I wonder the exactly query ecto creates

[josevalim]

@rafaeljesus so ecto is sending parameterized queries where $1 is interpolated. The values you see printed are Elixir values, hence the difference. One other option is for you to try doing the query directly with Postgrex and see if you can get better results. Here is an example: https://github.com/elixir-ecto/postgrex#example

[josevalim]

In Postgrex, you can try both:

Postgrex.query! pid, "INSERT INTO candidates (experiences) VALUES ('[{\"position\": 1}]')", []
Postgrex.query! pid, "SELECT * FROM candidates WHERE experiences @> '[{\"position\": 1}]'", []

and

Postgrex.query! pid, "INSERT INTO candidates (experiences) VALUES ('[{\"position\": 1}]')", []
Postgrex.query! pid, "SELECT * FROM candidates WHERE experiences @> $1", ["[{\"position\": 1}]"]

[rafaeljesus]

@josevalim thx, I tried your suggestion but I am having the following:

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "my_db")
res = Postgrex.query! pid, "SELECT * FROM candidates WHERE experiences @> '[{\"position\": 1}]'", []

Throws:

* (ArgumentError) no extension found for oid `3802`: %Postgrex.TypeInfo{array_elem: 0, base_type: 0, comp_elems: [], input: "jsonb_in", oid: 3802, output: "jsonb_out", receive: "jsonb_recv", send: "jsonb_send", type: "jsonb"}

I searched on postgrex issues and saw you implemented a json/jsonb extension, and I also tried:

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "my_db", extensions: [{Postgrex.Extension, :jsonb}])

But it is throws:

** (EXIT from #PID<0.517.0>) an exception was raised:
         ** (UndefinedFunctionError) function Postgrex.Extension.init/2 is undefined or private
             (postgrex) Postgrex.Extension.init(%{"DateStyle" => "ISO, MDY", "IntervalStyle" => "postgres", "TimeZone" => "Brazil/East", "application_name" => "", "client_encoding" => "UTF8", "integer_datetimes" => "on", "is_superuser" => "on", "server_encoding" => "UTF8", "server_version" => "9.5.3", "session_authorization" => "postgres", "standard_conforming_strings" => "on"}, :json)
             (postgrex) lib/postgrex/types.ex:57: anonymous fn/2 in Postgrex.Types.prepare_extensions/2
             (elixir) lib/enum.ex:1115: anonymous fn/4 in Enum.into/3
             (elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
             (elixir) lib/enum.ex:1121: Enum.into/4
             (postgrex) lib/postgrex/protocol.ex:551: Postgrex.Protocol.bootstrap_types/4
             (postgrex) lib/postgrex/protocol.ex:353: Postgrex.Protocol.handshake/2
             (db_connection) lib/db_connection/connection.ex:134: DBConnection.Connection.connect/2
             (connection) lib/connection.ex:622: Connection.enter_connect/5
             (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3

[josevalim]

It needs to be Postgrex.Extension.JSON.

On Monday, October 17, 2016, Rafael Jesus [email protected] wrote:

@josevalim https://github.com/josevalim thx, I tried your suggestion
but I am having the following:

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "my_db")
res = Postgrex.query! pid, "SELECT * FROM candidates WHERE experiences @> '[{\"position\": 1}]'", []

Throws:

• (ArgumentError) no extension found for oid 3802: %Postgrex.TypeInfo{array_elem: 0, base_type: 0, comp_elems: [], input: "jsonb_in", oid: 3802, output: "jsonb_out", receive: "jsonb_recv", send: "jsonb_send", type: "jsonb"}

I searched on postgrex issues and saw you implemented
https://github.com/elixir-ecto/postgrex/pull/61/files a json/jsonb
extension, and I also tried:

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "my_db", extensions: [{Postgrex.Extension, :jsonb}])

But it is throws:

** (EXIT from #PID<0.517.0>) an exception was raised:
** (UndefinedFunctionError) function Postgrex.Extension.init/2 is undefined or private
(postgrex) Postgrex.Extension.init(%{"DateStyle" => "ISO, MDY", "IntervalStyle" => "postgres", "TimeZone" => "Brazil/East", "application_name" => "", "client_encoding" => "UTF8", "integer_datetimes" => "on", "is_superuser" => "on", "server_encoding" => "UTF8", "server_version" => "9.5.3", "session_authorization" => "postgres", "standard_conforming_strings" => "on"}, :json)
(postgrex) lib/postgrex/types.ex:57: anonymous fn/2 in Postgrex.Types.prepare_extensions/2
(elixir) lib/enum.ex:1115: anonymous fn/4 in Enum.into/3
(elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
(elixir) lib/enum.ex:1121: Enum.into/4
(postgrex) lib/postgrex/protocol.ex:551: Postgrex.Protocol.bootstrap_types/4
(postgrex) lib/postgrex/protocol.ex:353: Postgrex.Protocol.handshake/2
(db_connection) lib/db_connection/connection.ex:134: DBConnection.Connection.connect/2
(connection) lib/connection.ex:622: Connection.enter_connect/5
(stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/elixir-ecto/ecto/issues/1748#issuecomment-254194235,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAAlbvjHjWXdu2Cq8mc8241lYyCyX1obks5q02rIgaJpZM4KXCnB
.

_José Valimwww.plataformatec.com.br
http://www.plataformatec.com.br/Founder and Director of R&D_

[rafaeljesus]

I did that, updated postgrex to 0.12.1 but it still:

{:ok, pid} = Postgrex.start_link(
  hostname: "localhost",
  username: "postgres",
  password: "postgres",
  database: "my_db",
  extensions: [{Postgrex.Extension.JSON, :jsonb}])

error:

* (UndefinedFunctionError) function Postgrex.Extension.JSON.init/2 is undefined (module Postgrex.Extension.JSON is not available)
    Postgrex.Extension.JSON.init(%{"DateStyle" => "ISO, MDY", "IntervalStyle" => "postgres", "TimeZone" => "Brazil/East", "application_name" => "", "client_encoding" => "UTF8", "integer_datetimes" => "on", "is_superuser" => "on", "server_encoding" => "UTF8", "server_version" => "9.5.3", "session_authorization" => "postgres", "standard_conforming_strings" => "on"}, :jsonb)

Also tried Postgrex.Extensions.JSON but:

     ** (EXIT from #PID<0.517.0>) an exception was raised:
       ** (FunctionClauseError) no function clause matching in Keyword.get/3
           (elixir) lib/keyword.ex:150: Keyword.get(:jsonb, :decode_binary, :copy)
           (postgrex) lib/postgrex/extensions/json.ex:25: Postgrex.Extensions.JSON.init/2
           (postgrex) lib/postgrex/types.ex:57: anonymous fn/2 in Postgrex.Types.prepare_extensions/2

[rafaeljesus]

Also tried with only extensions: [{Postgrex.Extension.JSON}])

11:30:03.996 [error] GenServer #PID<0.519.0> terminating
** (FunctionClauseError) no function clause matching in anonymous fn/1 in Postgrex.Types.prepare_extensions/2
    (postgrex) lib/postgrex/types.ex:56: anonymous fn({Postgrex.Extension.JSON}) in Postgrex.Types.prepare_extensions/2
    (elixir) lib/enum.ex:1115: anonymous fn/4 in Enum.into/3
    (elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:1121: Enum.into/4
    (postgrex) lib/postgrex/protocol.ex:551: Postgrex.Protocol.bootstrap_types/4
    (postgrex) lib/postgrex/protocol.ex:353: Postgrex.Protocol.handshake/2
    (db_connection) lib/db_connection/connection.ex:134: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3

[ericmj]

It should be: extensions: [{Postgrex.Extensions.JSON, library: Poison}]

[rafaeljesus]

@ericmj thx for comming up

{:ok, pid} = Postgrex.start_link(
     hostname: "localhost",
     username: "postgres",
     password: "postgres",
     database: "match_maker_dev",
     extensions: [{Postgrex.Extensions.JSON, Poison}])

Throws:

12:12:18.003 [error] GenServer #PID<0.519.0> terminating
** (FunctionClauseError) no function clause matching in Keyword.get/3
    (elixir) lib/keyword.ex:150: Keyword.get(Poison, :decode_binary, :copy)
    (postgrex) lib/postgrex/extensions/json.ex:25: Postgrex.Extensions.JSON.init/2
    (postgrex) lib/postgrex/types.ex:57: anonymous fn/2 in Postgrex.Types.prepare_extensions/2
    (elixir) lib/enum.ex:1115: anonymous fn/4 in Enum.into/3
    (elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:1121: Enum.into/4
    (postgrex) lib/postgrex/protocol.ex:551: Postgrex.Protocol.bootstrap_types/4
    (postgrex) lib/postgrex/protocol.ex:353: Postgrex.Protocol.handshake/2
    (db_connection) lib/db_connection/connection.ex:134: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3

[josevalim]

It should be:

extensions: [{Postgrex.Extensions.JSON, library: Poison}]

[rafaeljesus]

@josevalim @ericmj It did worked, but it is only returning data if I manually insert it on database and run the query in iex -S mix,

It is not returning data running test, maybe something with database sandbox I don't know,

But hey thats fine, I still think fragment("?@>?", c.experiences, ^position) should to the job as in production we may face complex queries,

Thanks for debugging time!

[ellisae]

recently had the same issue:
fragment("?@>?", c.experiences, ^position)
returns nothing

but this should work:
fragment("?->>? = ?", c.experiences, "position", ^position)

[rafaeljesus]

@ellisae Thank you for commenting out, I will check this mate!

[Ivor]

Thanks for the contributions in this thread. It helped me.
This worked for me when querying the value of a nested key in a :map field.

where([a], fragment("? #> '{key,nested_key}' = ?", a.my_map_field, ^value))

[silviurosu]

I am having a similar problem. I have a jsonb array instead of map. I tried this two ideas but without success:
1)
where: fragment("not ? @> '[?]'", c.exclusions, ^dish_id)
It generates this sql:
(not o0."exclusions" @> '[$2]')
and does not run:
** (Postgrex.Error) ERROR 22P02 (invalid_text_representation): invalid input syntax for type json
2)

value = "'[#{dish_id}]'"
where: fragment("not ? @> ?", c.exclusions, ^value)

This generates:
(not o0."exclusions" @> $2) [10318, "'[83055]'"]
It does not raise any error but the filter is not working.

[DuriDuri]

@silviurosu any luck with this?

[Ivor]

I've since discovered this post http://www.ubazu.com/using-postgres-jsonb-columns-in-ecto and the section there about searching by object has become my default way of querying JSONB. I have yet to find a case where it doesn't work and it is a lot more readable.

[kuroda]

After reading http://www.ubazu.com/using-postgres-jsonb-columns-in-ecto, I found a solution:

json = "[{\"position\": #{job.position}}]"
fragment("?@>?", c.experiences, ^json))

should be modified as

map = %{"position" => job.position}
fragment("?@>?", c.experiences, ^map))

[i-n-g-m-a-r]

map = %{"position" => job.position}

100 x :+1:
thank you

[kash1102]

@kuroda Thanks for posting but for my case it worked when i used map =[%{"position" => job.position}]