query jsonb field and variable


I have a jsonb field in my db which looks like

[%{id: 1, name: “test1”}, %{id: 2, name: “test2”}]

I need get all the records which contains id: 1

my working query:
where: fragment("? @@ ?", a.details, ^"$[*].id == 1")

however i need to pass a variable as id so i changed it to:
where: fragment("? @@ ?", a.details, ^"$[*].id == #{variable.id}")

I have read is not possible to pass a variable (Interpolating) to a fragment due to sql injection attacks
my variable comes from a safe source (it’s an id from incrementation)

so i changed my query to, but it wont work (it returns empty array)
where: fragment("? @@ ? = ?", a.details, ^"$[*].id", ^variable.id)

any ideas what is wrong?

Could be a type mismatch that needs to be set explicitly in your string fragment.

From fragment/1

It is very important to keep in mind that Ecto is unable to do any type casting described above when fragments are used. You can however use the type/2 function to give Ecto some hints:

fragment("lower(?)", p.title) == type(^title, :string) 

What happens if you rewrite the query as in the example, moving the equality operator to Ecto side and explicitly casting?

where: fragment("? @@ ?", a.details, ^"$[*].id") == type(^variable.id, :integer)