AlchemistCamp
Ecto Migration to add JSONB index
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.
Marked As Solved
cblavier
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
Also Liked
AlchemistCamp
Looks straightforward enough. I’ll just drop my query into the execute like so:
execute("CREATE INDEX ON comments((meta->'author'->>'id'));")
Thanks!
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"