Ecto JSONB Fragment Help

This is the first time I am submitting a question so be gentle :slight_smile:

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.

:wave:

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

:wave:

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!