This is the first time I am submitting a question so be gentle
I am trying to query a string value in a jsonb col with an Ecto fragment and see weather it is in an array of strings.
The psql which works looks something like this:
SELECT *
FROM someTable
WHERE someCol->'someKey' <@ '["AString", "AnotherString"]'
My fragment that I am trying to create looks like:
fragment("(?->>'someKey' <@ ?", u.someCol, ^myArrayOfStrings))
which is throwing a syntax error
Postgrex.Error) ERROR 42601 (syntax_error) syntax error at end of input
Thanks in advance for any assistance.
idi527
June 13, 2020, 5:26pm
2
Can you please post your whole query? Running just the fragment works for me.
def q do
import Ecto.Query
my_list_of_strings = ["a", "b"]
"test_table"
|> select([e], e.data)
|> where([e], fragment("? -> 'key' <@ ?", e.data, ^my_list_of_strings))
|> App.Repo.all()
end
produces
SELECT t0."data" FROM "test_table" AS t0 WHERE (t0."data" -> 'key' <@ $1) [["a", "b"]]
Btw, using ->>
instead of ->
doesnโt work for me, but with a different error:
ERROR 42704 (undefined_object) could not find range type for data type text
3 Likes
Sorry for the late reply but you are correct! the single chevron was the issue, the original query was correct. Thanks so much for confirming this and finding the solution!