Issue generating a JSONB query with @>

Questions / Help
,
1

I am having an issue with a JSONB query.
This works:

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

SQL generated:

WHERE (review @> '{"product": {"category": "Fitness"}}')

But this doesn’t

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

SQL generated

WHERE (review @> '{"product": {"category": 'Fitness'}}')

Notice the single and double quotes between the SQL generated.
How can I cast the value to double nstead of single quotes?

1 Like
2

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

3

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.

3 Likes
5

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…

6

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

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

Does that work?

7

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.

1 Like
8

@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… ) >.>

9

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.

5 Likes
10

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
  }
}
}
1 Like
11

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

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

1 Like
12

@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. :slight_smile: