When using postgres, I’d like to query a table where a JSONB column contains a JSON document with a field called members whose value is a list of strings. The query should return all rows where the members list in that column contains a string entry that matches a given string.

This SQL query works:

select * from typed_groups where members @> jsonb_build_object('members', jsonb_build_array('member_1'));

But I haven’t come up with an ecto query that works. I’ve tried this:

   query =
      from g in __MODULE__,
        where:
            fragment(
              "? @> ?",
              g.members,
              ^"jsonb_build_object('members', jsonb_build_array('#{member_id}'))::jsonb"
            ),
        select: g

And I’ve tried building the interpolated string outside the query. All return no rows.