对于任何偶然发现的人。我想出了一个使用ActiveRecord和Postgres的JSON数据类型的查询列表。随时对其进行编辑以使其更加清晰。
以下使用的JSON运算符的文档：https : //www.postgresql.org/docs/current/functions-
json.html。
# Sort based on the Hstore data:Post.order("data->'hello' DESC")=> #<ActiveRecord::Relation [ #<Post id: 4, data: {"hi"=>"23", "hello"=>"22"}>, #<Post id: 3, data: {"hi"=>"13", "hello"=>"21"}>, #<Post id: 2, data: {"hi"=>"3", "hello"=>"2"}>, #<Post id: 1, data: {"hi"=>"2", "hello"=>"1"}>]># Where inside a JSON object:Record.where("data ->> 'likelihood' = '0.89'")# Example json object:r.column_data=> {"data1"=>[1, 2, 3], "data2"=>"data2-3", "array"=>[{"hello"=>1}, {"hi"=>2}], "nest"=>{"nest1"=>"yes"}}# Nested search:Record.where("column_data -> 'nest' ->> 'nest1' = 'yes' ")# Search within array:Record.where("column_data #>> '{data1,1}' = '2' ")# Search within a value that's an array:Record.where("column_data #> '{array,0}' ->> 'hello' = '1' ")# this only find for one element of the array.# All elements:Record.where("column_data ->> 'array' LIKE '%hello%' ") # badRecord.where("column_data ->> 'array' LIKE ?", "%hello%") # good
