Database fragment query error "parameters must be of length 0 for query"

Question

I am trying to query a json array in the building table, jsonb metadata column, and within google_place_ids property, which is an array.

Elixir is giving me an error:

Here's the query the fragment is generating:

SELECT b0."id", b0."base_sku", b0."name", b0."logo", b0."email", b0."active", b0."sync", b0."building_group_id", b0."operating_region_id", b0."building_package_id", b0."metadata", b0."location", b0."inserted_at", b0."updated_at" FROM "buildings" AS b0 WHERE (b0."metadata"->'google_place_ids' @> '["$1"]') AND (b0."active" = TRUE) ["1234231223123"]

Here's the code:

defp query_by_google_place_id(query, google_place_id) do
from(b in query,
    where: fragment("?->'google_place_ids' @> '[\"?\"]'", b.metadata,  ^google_place_id),
    where: b.active == true,
    limit: 1)
end

Here is the error:

[error] #PID<0.1340.0> running Proxy.InstrumentedPlug terminated
Server: localhost:4000 (http)
Request: GET /something/google_place_id/1234231223123
** (exit) an exception was raised:
    ** (ArgumentError) parameters must be of length 0 for query %Postgrex.Query{columns: 

Answer 1

This is not how PostgreSQL will understand it. The problem is that Ecto's fragment do not know anything about SQL (or other query language) you can use there, so it ends treating ? as a query parameter while PostgreSQL treats it as a raw string "$1". What you need to do is to pass string directly in form of:

fragment("?->'google_place_ids' @> ?", b.metadata, ^"[\"#{google_place_id}\"]")