Ecto query of a JSONB column

Questions / Help
1

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.

2

You need a fragment for the jsonb_build_object and jsonb_build_array as well.

3

How would that look? since those are inside a fragment themselves?

4

You can either use one big fragment, where you put the whole sql in one string and have more parameters or you can also provide nested fragments as parameters of an outer fragment.

fragment("? @> jsonb_build_object(?, jsonb_build_array(?))", g.members, "members", member_id)
# or
fragment(
  "? @> ?", 
  g.members, 
  fragment(
    "jsonb_build_object(?, ?)", 
    "members", 
    fragment("jsonb_build_array(?)", member_id)
  )
)

The latter is useful because you can wrap fragment usage in macros and essentually extend the available APIs to use in query building: Ecto.Query.API — Ecto v3.11.2

3 Likes
5

Cool, thanks!! Both of those suggestions work great if I put in a literal string for member_id in that last fragment. If I pin it to a variable with ^member_id instead of a literal string, I get an indeterminate_datatype error.

To fix this, I had to add ::text after the final ? as a type hint.

1 Like
6

That’s where postgres can no longer infer the type, because you started to use a paramter. You can use Ecto.Query.API — Ecto v3.11.2 to force a type.

2 Likes