Elixir Ecto - PostgreSQL jsonb Functions

Question

I am in the process of converting a Ruby on Rails API over to Elixir and Phoenix. In my Postgres database, I have a table with a jsonb column type. One of the keys in the json is an array of colors. For example:

{"id": 12312312, "colors": ["Red", "Blue", "White"]}

What I am trying to do from Ecto is query my table for all records that contain the colors Red or Blue. Essentially, recreate this query:

select * from mytable where data->'colors' ?| array['Red', 'Blue']

I'm having some difficulties constructing this query with Ecto. Here is what I have:

Note: "value" will be a pipe delimited list of colors

  def with_colors(query, value) do
    colors = value 
      |> String.split("|")
      |> Enum.map(fn(x) -> "'#{x}'" end)
      |> Enum.join(", ")

    # colors should look like "'Red', 'Blue'"

    from c in query,
    where: fragment("data->'colors' \\?| array[?]", ^colors))
  end

This is currently not working as expected. I am having issues with the replacement question mark, as it seems to wrap additional quotes around my field. What is the proper way to do this use fragment? Or maybe there is a better way?

I'm going to run into this problem again because I'm also going to have to recreate this query:

select * from mytable where data->'colors' @> '["Red", "Blue"]'

Andrew Backes Andrew Backes · Accepted Answer · 2016-09-02T02:18:17

I have found a solution to my problem.

def with_colors(query, value) do
  colors = value 
    |> String.split("|")

  from c in query,
  where: fragment("data->'colors' \\?| ?", ^colors))
end

Elixir Ecto - PostgreSQL jsonb Functions

1 Answers