Ecto not allowing string interpolation in fragments?

Questions / Help
19

Hi all, I’ve spent a lot of time with this thread and with Ecto fragments - how to regex match dynamically? , but I still can’t quite tell if I’ve got the same problem. If not, I’ll move this over to a new question. Here’s the context:

We’re using Postgres 12 and the jsonb_path_exists function to find records with a specific value in a json column. (https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE)

def search_text(query, %{"search_string" => _string_not_used}) do
  from(r in query,
    where:
      fragment(
        "jsonb_path_exists(?, '$.** \\? (@ == \"some_text\")')",
        r.body
      )
  )
end

The above code works like a charm, but when I plug in the variable interpolation like so:

def search_text(query, %{"search_string" => search_string}) do
  from(r in query,
    where:
      fragment(
        "jsonb_path_exists(?, '$.** \\? (@ == \"?\")')",
        r.body,
        ^search_string
      )
  )
end

the resulting query is {"SELECT r0.\"id\", r0.\"project_id\", r0.\"inserted_at\" FROM \"receipts\" AS r0 WHERE (r0.\"project_id\" = $1) AND (r0.\"inserted_at\" >= $2) AND (r0.\"inserted_at\" < $3) AND (jsonb_path_exists(r0.\"body\", '$.** ? (@ == \"$4\")')) ORDER BY r0.\"id\" DESC", [1, ~U[2020-03-31 23:17:04Z], ~U[2020-04-30 23:17:28Z], "variable_text"]} and I get:

%ArgumentError{
  message: "parameters must be of length 3 for query %Postgrex.Query{
    cache: :reference, columns: [\"count\"],
    name: \"ecto_7266\",
    param_formats: [:binary, :binary, :binary],
    param_oids: [23, 1114, 1114],
    param_types: [Postgrex.Extensions.Int4, Postgrex.Extensions.Timestamp, Postgrex.Extensions.Timestamp],
    ref: #Reference<0.1575397138.1124859907.232543>, result_formats: [:binary], result_oids: [20],
    result_types: [Postgrex.Extensions.Int8],
    statement: \"SELECT count('*') FROM \\\"receipts\\\" AS r0 WHERE (r0.\\\"project_id\\\" = $1) AND (r0.\\\"inserted_at\\\" >= $2) AND (r0.\\\"inserted_at\\\" < $3) AND (jsonb_path_exists(r0.\\\"body\\\", '$.** ? (@ == \\\"$4\\\")'))\",
    types: {Postgrex.DefaultTypes, #Reference<0.1575397138.1124990979.26616>}
  }"
}

I gather this has to do with Postgrex (or Ecto) passing in my variable as 'variable_text', resulting in "'variable_text'" in Postgres, and I’ve tried to use unquote(…) or the like, but can’t quite figure it out. Is there something big that I’m missing, and am I reading the right thread?

Would custom Ecto types be a better place to look?

Thanks so much in advance. @Frogglet, if that final @NobbZ post worked for you, would you mind sharing your code?

1 Like