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?