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 · Accepted Answer · 2016-09-02 02:18:17Z

5

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

answered Sep 2, 2016 at 2:18

Andrew Backes

1,9144 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Elixir Ecto - PostgreSQL jsonb Functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related