在 Elixir/Ecto 中使用 PostgreSQL JSON 数据类型

`Elixir` `PostgreSQL` `index` `JSON` `索引` `Ecto` --- PostgreSQL 提供了丰富的数据类型。在 9.3 版本之后,PostgreSQL 内置了 JSON 数据类型以及一系列配套的用以操作 JSON 的内置函数。对于一些灵活的数据,使用 JSON 格式来存储就再好不过了,而且还可以对其添加索引,所以在性能上也有一定程度上的保证。 ### 在 Elixir/Ecto 中使用 JSON 格式 Ecto 可以直接将 Elixir 中的 map 格式转为 JSON 进行存储,所以使用起来非常的“原生”。 首先建立 Model schema ```elixir schema "users" do field :name, :string field :content, :map # 指定 content 为 map 格式 timestamps() end ``` 然后建立 Migration change ```elixir def change do create table(:users) do add :name, :string add :content, :json # 指定 content 为 json 格式 timestamps() end end ``` 这样就可以进行存储了 ```elixir %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 ``` 当然直接查询出来也是 map 格式,非常自然。 如果你想查 select map 中的某一项也是可以的! ```elixir query = Ecto.Query.from user in BlindingdarkSpace.User, where: fragment("?->>'real_name' = ?", user.content, "zhua_kuang") ``` 双箭头 `->>` 为 PostgreSQL 提供的取 JSON 值的语法。(对应的还有用于取多级 JSON 的 `->`,以及取数组的 `#>`) 根据 PostgreSQL 的说法,双箭头 `->>` 查出来的都是 TEXT 类型。也就是说即使你存的时候是 INT 类型,双箭头取出来却是 TEXT。 比如 phone_number 这个字段,如果我们这样写 where 子句是报错的,会提示 INT 和 TEXT 不匹配。 ```elixir where: fragment("?->>'phone_number' = ?", user.content, 123_1234_5678) ``` 怎么办?需要使用双冒号 `::` 进行类型转换。 ```elixir where: fragment("(?->>'phone_number')::INT = ?", user.content, 123_1234_5678) ``` 这里的 `(?->>'phone_number')::INT` 就可以把 `?->>'phone_number'` 也就是把 `user.content.phone_number` 的值转为 INT。 或者将第二个参数的 INT 转为 TEXT ```elixir where: fragment("?->>'phone_number' = ?::TEXT", user.content, 123_1234_5678) ``` 这里省略了小括号,也就是把紧挨着的那个值进行转换。把 INT 转 TEXT 更灵活一点,这样查询的时候 TEXT 和 INT 都能进行正常的查询(TEXT 转 TEXT 还是 TEXT)。也就是说下面这样也是对的。 ```elixir where: fragment("?->>'phone_number' = ?::TEXT", user.content, "12312345678") ``` ### 建立 JSON 索引 为了提升查询性能,我们可以在 JSON 列上建立索引。 增加新的 Migration change,创建索引。 ```elixir create index(:users, ["(content->>'phone_number')"], unique: true) ``` 这样就在 `content.phone_number` 上建立了一个带有唯一性约束的索引。 ### 结语 目前 Ecto 好像还没有直接操作 JSON 格式的方法,只能通过 `fragment` 来直接写 SQL,我相信以后会有封装过后更直接的操作方式。 --- #### 参考链接 [Gabriel Jaldon - Using JSON Type in Ecto](http://gabrieljaldon.com/articles/using-json-type-in-ecto.html) [Dave Clark - What can you do with PostgreSQL and JSON?](http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/) [Stack Overflow - How to create index on json field in Postgres 9.3](https://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3)