Postgres JSON 数据类型 Rails 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22667401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres JSON data type Rails query
提问by Mohamed El Mahallawy
I am using Postgres' json data type but want to do a query/ordering with data that is nested within the json.
我正在使用 Postgres 的 json 数据类型，但想对嵌套在 json 中的数据进行查询/排序。
I want to order or query with .where on the json data type. For example, I want to query for users that have a follower count > 500 or I want to order by follower or following count.
我想在 json 数据类型上使用 .where 进行订购或查询。例如，我想查询关注者计数 > 500 的用户，或者我想按关注者或关注者计数进行排序。
Thanks!
谢谢！
Example:
例子：
model User
data: {
"photos"=>[
{"type"=>"facebook", "type_id"=>"facebook", "type_name"=>"Facebook", "url"=>"facebook.com"}
],
"social_profiles"=>[
{"type"=>"vimeo", "type_id"=>"vimeo", "type_name"=>"Vimeo", "url"=>"http://vimeo.com/", "username"=>"v", "id"=>"1"},
{"bio"=>"I am not a person, but a series of plants", "followers"=>1500, "following"=>240, "type"=>"twitter", "type_id"=>"twitter", "type_name"=>"Twitter", "url"=>"http://www.twitter.com/", "username"=>"123", "id"=>"123"}
]
}
回答by Mohamed El Mahallawy
For any who stumbles upon this. I have come up with a list of queries using ActiveRecord and Postgres' JSON data type. Feel free to edit this to make it more clear.
对于任何偶然发现这一点的人。我想出了一个使用 ActiveRecord 和 Postgres 的 JSON 数据类型的查询列表。随意编辑它以使其更清晰。
Documentation to the JSON operators used below: https://www.postgresql.org/docs/current/functions-json.html.
下面使用的 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%' ") # bad
Record.where("column_data ->> 'array' LIKE ?", "%hello%") # good
回答by guapolo
According to this http://edgeguides.rubyonrails.org/active_record_postgresql.html#jsonthere's a difference in using ->and ->>:
根据这个http://edgeguides.rubyonrails.org/active_record_postgresql.html#json使用->和有区别->>：
# db/migrate/20131220144913_create_events.rb
create_table :events do |t|
t.json 'payload'
end
# app/models/event.rb
class Event < ActiveRecord::Base
end
# Usage
Event.create(payload: { kind: "user_renamed", change: ["Hyman", "john"]})
event = Event.first
event.payload # => {"kind"=>"user_renamed", "change"=>["Hyman", "john"]}
## Query based on JSON document
# The -> operator returns the original JSON type (which might be an object), whereas ->> returns text
Event.where("payload->>'kind' = ?", "user_renamed")
So you should try Record.where("data ->> 'status' = 200 ")or the operator that suits your query (http://www.postgresql.org/docs/current/static/functions-json.html).
因此，您应该尝试Record.where("data ->> 'status' = 200 ")使用适合您查询的运算符（http://www.postgresql.org/docs/current/static/functions-json.html）。
回答by Meekohi
Your question doesn't seem to correspond to the data you've shown, but if your table is named usersand datais a field in that table with JSON like {count:123}, then the query
您的问题似乎与您显示的数据不符，但如果您的表已命名users并且data是该表中的字段，其格式为JSON {count:123}，则查询
SELECT * WHERE data->'count' > 500 FROM users
SELECT * WHERE data->'count' > 500 FROM users
will work. Take a look at your database schema to make sure you understand the layout and check that the query works before complicating it with Rails conventions.
将工作。查看您的数据库架构以确保您了解布局并检查查询是否有效，然后再将其与 Rails 约定复杂化。
