In my opinion the best way to solve issues like this is to first decide how you would represent it in the database if you weren’t using Ecto. Keep in mind Ecto is just defining a mapping between Elixir and your persistence layer. If you don’t know what you want to do in your persistence layer you don’t know what you want to do in Ecto.

I would go for two maps handled by a homemade Ecto.Type:

• key => value
• key => index

When you want to access the key/value pairs in order, just use the second map.

Those two maps can also be wrapped in a single map, no need for two DB table columns either.

iex(1)> kv = [%{a: 12}, %{b: 102}, %{c: 9}]
[%{a: 12}, %{b: 102}, %{c: 9}]
iex(2)> Enum.find_value(kv, fn kv -> kv[:b] end)
102

Keyword lists or just ordered key/value pairs are unknown to RDBMS engines as far as I am aware so even if you don’t code a custom Ecto.Type you’d still end up writing the same code anyway but someplace else.

So go for something that would be stored as JSON / JSONB in the DB in the form of %{v: keys_to_values, i: keys_to_indices} and have proper cast, dump and load functions and you’re golden. You might even open-source it in the form of an e.g. ecto_ordered_map library, if you feel generous.

Does it matter what the data stored in the JSON looks like, do you need to issue db queries againt it?

If not then an option could be to unzip the KV list into a 2d array in the changeset operation before storing/updating, and post-process the repo query to zip it back up when taking it out again.

fruits = ["apple", "banana", "orange"]
counts = [3, 1, 6]
Enum.zip(fruits, counts)
[{"apple", 3}, {"banana", 1}, {"orange", 6}]

from zipping cheatsheet

Since you’re contemplating a Keyword list I assume your data structure has known atom fields, in which case you can also transform the keys into known atoms during the zipping, otherwise you’d run into atom table issues.

But if keys are known and have set order, then you could just store the values as a sparse array and rehydrate your Keyword list from there

This is probably a good candidate for a custom Ecro type, but without knowing more about your data structure it’s hard to give a more definitive answer.