Elixir PostgreSQL index JSON 索引 Ecto

PostgreSQL 提供了豐富的數(shù)據(jù)類型。在 9.3 版本之后，PostgreSQL 內(nèi)置了 JSON 數(shù)據(jù)類型以及一系列配套的用以操作 JSON 的內(nèi)置函數(shù)花竞。對(duì)于一些靈活的數(shù)據(jù)岩瘦，使用 JSON 格式來(lái)存儲(chǔ)就再好不過(guò)了，而且還可以對(duì)其添加索引唇敞，所以在性能上也有一定程度上的保證蔗草。

在 Elixir/Ecto 中使用 JSON 格式

Ecto 可以直接將 Elixir 中的 map 格式轉(zhuǎn)為 JSON 進(jìn)行存儲(chǔ)，所以使用起來(lái)非常的“原生”疆柔。
首先建立 Model schema

schema "users" do
   field :name, :string
   field :content, :map # 指定 content 為 map 格式

   timestamps()
end

然后建立 Migration change

def change do
  create table(:users) do
    add :name, :string
    add :content, :json # 指定 content 為 json 格式

    timestamps()
  end
end

這樣就可以進(jìn)行存儲(chǔ)了

%BlindingdarkSpace.User{
  name: "blindingdark",
  content: %{
    real_name: "zhua_kuang",
    phone_number: 123_1234_5678,
    home_page: "http://blindingdark.space",
    github: "https://github.com/BlindingDark"
  }
}
|> BlindingdarkSpace.User.changeset(%{})
|> BlindingdarkSpace.Repo.insert

當(dāng)然直接查詢出來(lái)也是 map 格式咒精，非常自然。
如果你想查 select map 中的某一項(xiàng)也是可以的旷档！

query =
  Ecto.Query.from user in BlindingdarkSpace.User,
    where: fragment("?->>'real_name' = ?", user.content, "zhua_kuang")

雙箭頭 ->> 為 PostgreSQL 提供的取 JSON 值的語(yǔ)法模叙。（對(duì)應(yīng)的還有用于取多級(jí) JSON 的 ->，以及取數(shù)組的 #>）
根據(jù) PostgreSQL 的說(shuō)法鞋屈，雙箭頭 ->> 查出來(lái)的都是 TEXT 類型范咨。也就是說(shuō)即使你存的時(shí)候是 INT 類型，雙箭頭取出來(lái)卻是 TEXT厂庇。
比如 phone_number 這個(gè)字段渠啊，如果我們這樣寫 where 子句是報(bào)錯(cuò)的，會(huì)提示 INT 和 TEXT 不匹配权旷。

where: fragment("?->>'phone_number' = ?", user.content, 123_1234_5678)

怎么辦替蛉？需要使用雙冒號(hào) :: 進(jìn)行類型轉(zhuǎn)換。

where: fragment("(?->>'phone_number')::INT = ?", user.content, 123_1234_5678)

這里的 (?->>'phone_number')::INT 就可以把 ?->>'phone_number' 也就是把 user.content.phone_number 的值轉(zhuǎn)為 INT。

或者將第二個(gè)參數(shù)的 INT 轉(zhuǎn)為 TEXT

where: fragment("?->>'phone_number' = ?::TEXT", user.content, 123_1234_5678)

這里省略了小括號(hào)躲查，也就是把緊挨著的那個(gè)值進(jìn)行轉(zhuǎn)換它浅。把 INT 轉(zhuǎn) TEXT 更靈活一點(diǎn)，這樣查詢的時(shí)候 TEXT 和 INT 都能進(jìn)行正常的查詢(TEXT 轉(zhuǎn) TEXT 還是 TEXT)镣煮。也就是說(shuō)下面這樣也是對(duì)的罚缕。

where: fragment("?->>'phone_number' = ?::TEXT", user.content, "12312345678")

建立 JSON 索引

為了提升查詢性能，我們可以在 JSON 列上建立索引怎静。
增加新的 Migration change邮弹，創(chuàng)建索引。

create index(:users, ["(content->>'phone_number')"], unique: true)

這樣就在 content.phone_number 上建立了一個(gè)帶有唯一性約束的索引蚓聘。

結(jié)語(yǔ)

目前 Ecto 好像還沒(méi)有直接操作 JSON 格式的方法腌乡，只能通過(guò) fragment 來(lái)直接寫 SQL，我相信以后會(huì)有封裝過(guò)后更直接的操作方式夜牡。

參考鏈接

Gabriel Jaldon - Using JSON Type in Ecto
Dave Clark - What can you do with PostgreSQL and JSON?
Stack Overflow - How to create index on json field in Postgres 9.3