Issue generating a JSONB query with @>

@jamesaspinwall You should mark your ‘map’ post as the solution post so others can find it faster in the future, that’s a very useful nugget of info.

Ah I didn’t know it would accept that there, awesome!

And there are even ways to optimize it further, PostgreSQL is awesome.

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
  }
}
}

I think I solved my issue. I can use the map type itself.

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

Beautiful and simple code.

@michalmuskala or anyone else… What’s the magical incantation to get Ecto to accept this? I’m brain-farting currently… (I’m 2 hours from a week break… ) >.>

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.

Actually it might need to be typed as jsonb or so?

where: fragment(~s(review @> ?::jsonb), ^p)

Does that work?

I thought I got it, but no cigar.
This works:

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

but this doesn’t

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

It seems that I need to give fragment a hint on the type :string, which I can’t figure it out.
Ugh…

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.

Have you tried using ::jsonb or ::text? What’s wrong with 'Fitness'?