Ecto Migration to add JSONB index

Questions / Help
,
1

How do you create an index on a JSONB field in an Ecto migration? I’m trying to do the equivalent of this:

CREATE TABLE comments(id INT, body TEXT, data JSONB); 
CREATE INDEX ON comments((meta->'author'->>'id'));

What should I fill into the ??? below?

def change do
  create table(:comments) do
    add :meta, map
    add :body, text
    #...
    timestamps()
  end

  create index(???, ???)
end

Note, that the meta is coming in is user generated, so I’m not 100% sure what the structure will be to begin with and will likely want to add more indexes as time goes by.

1 Like
2

If I were you, I would create the index with a raw SQL query. As in following PostGIS index creation

defmodule MyApp.AddPoiPostgisIndices do
  use Ecto.Migration

  def up do
    execute("CREATE INDEX pois_coordinates_index ON pois USING GIST(coordinates);")
  end

  def down do
    execute("DROP INDEX pois_coordinates_index")
  end
end
3 Likes
3

Looks straightforward enough. I’ll just drop my query into the execute like so:
execute("CREATE INDEX ON comments((meta->'author'->>'id'));")

Thanks!

2 Likes