[ruby on rails] array、jsonb字段添加查询

一、jsonb

# 新增
add_column :shi_tis, :setting, :jsonb, default: {}

# string转jsonb
def change
  change_column :users, :setting, :jsonb, using: 'setting::jsonb', default: {}
end

# 加索引
add_index :users, :setting, using: :gin # 这样就为setting jsonb字段创建了一个索引。
# 后续就可以利用这个索引来快速查询内部字段值:
User.where("setting->>'name' = ?", 'LiLei')
# 如果字段较大,也可以只为某个内部路径添加索引:
add_index :users, ("setting->'name'"), using: :gin

jsonb查询

 #<User:0x000000010b37ebd0> {
                :id => 2,
             :mobile => nil,
        :setting => {
            "name" => "LiLei",
            "age" => 2,
            "wechat" => { "nickname" => "sky", "openid" => 'x23212x' } 
        }
 }
User.where("setting @> ?", { name: 'LiLei' }.to_json)
User.where("setting ->> 'name' = ?", 'LiLei')
User.where("setting -> 'wechat' = ?", { nickname: 'sky' }.to_json)
User.where("setting -> 'wechat' ->> 'nickname' = ?", 'sky')

• 使用@>匹配完整的嵌套结构
  使用->将值提取为json类型,可以对嵌套字段继续查询
  使用->>提取叶子值进行简单查询
  两者满足不同的查询需求,@>范围更广,而->>通常性能更好些。

json和jsonb字段在Postgres数据库层面有以下区别:

• json
  使用文本存储JSON数据,可以进行有选择的更新。
  查询需要先解析JSON字符串再进行操作,效率较低。
  不支持索引,不能直接对内部字段进行查询。

• jsonb
  将JSON数据二进制化存储,整个字段作为一个值进行更新。
  内部使用类似于哈希的结构存储,所以查询效率很高。
  支持索引,可以直接对内部字段建立索引快速查询。

• 具体差异:
  存储:json是文本,jsonb是二进制代码。jsonb占用空间小,查询效率更高。
  更新:json支持部分更新,jsonb只能整体更改。
  查询:json需要deserialize,jsonb支持索引直接查询内部字段。
  索引:只有jsonb支持索引内部字段。

• 所以在Rails中:
  json更适用于不太更新的场景,如配置等。
  jsonb适用于查询较多,内部字段需要索引的场景。

二、Array

create_table :books do |t|
  t.string 'title'
  t.string 'tags', array: true
  t.integer 'ratings', array: true
end
add_index :books, :tags, using: 'gin'
add_index :books, :ratings, using: 'gin'

Book.create title: "Brave New World",
            tags: ["fantasy", "fiction"],
            ratings: [4, 5]

# 包含fantasy的tags
Book.where("'fantasy' = ANY (tags)")

# 包含["fantasy", "fiction"]2个
Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"])

# 匹配["fantasy", "fiction"]中任意一个
Book.where("tags && ARRAY[?]::varchar[]", ["fantasy", "fiction"])

## Books with 3 or more ratings
Book.where("array_length(ratings, 1) >= 3")