Querying embeds_many no result with interpolation

Questions / Help
,
1

I have a embeds_many field in Postgresql jsonb and I am trying to query it using fragment like this:

 def find(key, val) do
     q = from p in Person,
           where: fragment("meta_tags @> ?", ^"'[{\"#{key}\":\"#{val}\"}]'"),
           select: p
     Repo.all(q)
 end

I don’t understand why hardcoded value in the fragment("meta_tags @> '[{"type":"test"}]'") works but will return no result once I use interpolated values from the function input.

The logs are as below:

Not returning result with interpolated string.

[debug] QUERY OK source="world_persons" db=15.1ms
SELECT w0."id", w0."name", w0."salutation", w0."original_name", w0."gender", w0."dob" w0."email", w0."active", w0."inserted_at", w0."updated_at" FROM "persons" AS w0 WHERE (meta_tags @> $1) ["'[{\"type\":\"test\"}]'"]
[]

With results when I hardcode the value.

[debug] QUERY OK source="world_persons" db=5.2ms decode=0.1ms
SELECT w0."id", w0."name", w0."salutation", w0."original_name", w0."gender", w0."dob", w0."email", w0."active", w0."inserted_at", w0."updated_at" FROM "persons" AS w0 WHERE (meta_tags @> '[{"type":"test"}]') []
(... results)

This won’t work with hardcoded value

where: fragment("meta_tags @> ?", ^"[{\"type\":\"test\"}]"),

This works without the interpolated operator

where: fragment("meta_tags @> ?", "[{\"type\":\"test\"}]"),

From a suggestion in stackoverflow, I cast the query to text and then to jsonb and that works.

where: fragment("meta_tags @> ?::text::jsonb", ^"[{\"#{key}\":\"#{val}\"}]"),

What have I missed in this query to make this work without the casting trick?

1 Like