Indexing of JSONB embedded Ecto2 models in Postgres 9.4+

Question

I'm not clear on how to index embedded structs stored as JSONB with Ecto2/Postgres 9.4+

I have a schema with two embedded structs using embeds_one and embeds_many. They are ecto :map fields represented in Postgres as JSONB. I am wondering how I can be sure they are indexed (using Gin?) for speedy queries? I am not sure if this happens automagically, if I need to add an index to my migration or if I need to do it manually using psql etc..

Just looking for clarification on how this works. Thanks!

defmodule App.Repo.Migrations.CreateClient
  def change do
    create table(:clients) do
      add :name, :string
      add :settings, :map
      add :roles, {:array, :map}, default: []
      timestamps()
    end

    // This works for normal schema/model fields
    create index(:clients, [:name], unique: true, using: :gin)

    // BUT CAN I INDEX MY EMBEDS HERE?
    // GUESS: 
    create index(:clients, [:settings], using: :gin)
  end
end

defmodule App.Client do
  schema "client" do
    field :name, :string
    embeds_one  :settings, Settings // single fixed schema "Settings" model
    embeds_many :roles, Role        // array of "Role" models 
  end
end

defmodule Settings do
  use Ecto.Model
  embedded_schema do           // ALSO 
    field :name, :string       // are types relevant?          
    field :x_count, :integer   // stored as strings (INDEXED?)
    field :is_active, :boolean // deserialized via cast?
  end
end

defmodule Role do
  use Ecto.Model
  embedded_schema do      
    field :token   
    field :display_english  
    field :display_spanish
  end
end

NoDisplayName · Accepted Answer · 2016-08-21 21:26:51Z

3

I think you just need to add this:

create index(:clients, [:name], unique: true, using: :gin)

to your migration file.

Or if the index sql statement is gonna be complicated, you could do it with execute so it would be something like this:

execute("CREATE INDEX clients_name_index ON clients USING GIN (name)")

I have not tested it but I believe it should work.

answered Aug 21, 2016 at 21:26

NoDisplayName

15.8k12 gold badges69 silver badges103 bronze badges

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

3 Comments

errata Over a year ago

that is definitely right for the top level fields in the Client model but I am asking about indexing the JSONB :map fields

NoDisplayName Over a year ago

Well, go here and look at the jsonb Indexing section, the syntax is mostly the same for jsonb columns as for the usual ones so my first example should work unless you add some specific options for the index, for which you'd use execute

blnc Over a year ago

here is an example with elixir/ecto. and another for querying the maps

Collectives™ on Stack Overflow

Indexing of JSONB embedded Ecto2 models in Postgres 9.4+

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related