Issue generating a JSONB query with @>

Actually the problem here is that the ? is being put inside of a value instead of ‘being’ the value itself, and Ecto doesn’t support that. Rather you’d need to do something like build the json out of the query and put it in en-masse, something like:

where: fragment(~s(review @> ?), Jason.to_string_or_whatever!(%{product: %{category: "Fitness"}})

Or so.

This works:

Repo.all from r in Review, 
where: fragment(~s(review @> ?), ~s({"product": {"category": "Fitness & Yoga"}}))

generates:

WHERE (review @> '{"product": {"category": "Fitness & Yoga"}}') []

returns a list of structs

The second:

p = ~s({"product": {"category": "Fitness & Yoga"}})
Repo.all from r in Review, 
where: fragment(~s(review @> ?), ^p)

generates an SQL:

WHERE (review @> $1) ["{\"product\": {\"category\": \"Fitness & Yoga\"}}"]

returns empty list

Your suggestion

p = ~s({"product": {"category": "Fitness & Yoga"}})
Repo.all from r in Review, 
where: fragment(~s(review @> ?::jsonb), ^p)

generates:

WHERE (review @> $1::jsonb) ["{\"product\": {\"category\": \"Fitness & Yoga\"}}"]

returns empty result as the second.

For those interested in the jsonb performance:

debug] QUERY OK source="reviews" db=1.0ms
SELECT r0."id", r0."review" FROM "reviews" AS r0 WHERE (review @> $1 
) [%{product: %{category: "Fitness & Yoga"}}]

The table contains almost 600,000 records.
The typical review looks like:

%Review{
__meta__: #Ecto.Schema.Metadata<:loaded, "reviews">,
id: 398313,
review: %{
  "customer_id" => "A2VK03UD8VHFTT",
  "product" => %{
    "category" => "Fitness & Yoga",
    "group" => "DVD",
    "id" => "B00005T30Y",
    "sales_rank" => 22142,
    "similar_ids" => ["B00004U2MW", "006016848X", "B0007R4T3U",
     "B0002OXVBO"],
    "subcategory" => "General",
    "title" => "Men Are from Mars, Women Are from Venus"
  },
  "review" => %{
    "date" => "1998-09-15",
    "helpful_votes" => 6,
    "rating" => 5,
    "votes" => 6
  }
}
}