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:
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.
Can you please post your whole query? Running just the fragment works for me.
def q do
my_list_of_strings = ["a", "b"]
|> select([e], e.data)
|> where([e], fragment("? -> 'key' <@ ?", e.data, ^my_list_of_strings))
SELECT t0."data" FROM "test_table" AS t0 WHERE (t0."data" -> 'key' <@ $1) [["a", "b"]]
->> 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
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!